We have different editions of SQL Server, where SQL Server Express is free to download and use.
SQL Server uses T-SQL (Transact-SQL). T-SQL is Microsoft's proprietary extension to SQL. T-SQL is very similar to standard SQL, but in addition it supports some extra functionality, built-in functions, etc. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc.
SQL Server consists of a Database Engine and a Management Studio (and lots of other stuff which we will not mention here). The Database engine has no graphical interface - it is just a service running in the background of your computer (preferable on the server). The Management Studio is graphical tool for configuring and viewing the information in the database. It can be installed on the server or on the client (or both).
SQL Server Management Studio
SQL Server Management Studio is a GUI tool included with SQL Server for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools that work with objects and features of the server. As mentioned earlier, version of SQL Server Management Studio is also available for SQL Server Express Edition, for which it is known as SQL Server Management Studio Express.
A central feature of SQL Server Management Studio is the Object Explorer, which allows the user to browse, select, and act upon any of the objects within the server. It can be used to visually observe and analyze query plans and optimize the database performance, among others. SQL Server Management Studio can also be used to create a new database, alter any existing database schema by adding or modifying tables and indexes, or analyze performance. It includes the query windows which provide a GUI based interface to write and execute queries.
When creating SQL commands and queries, the “Query Editor” (select “New Query” from the Toolbar) is used (shown in the figure above).
With SQL and the “Query Editor” we can do almost everything with code, but sometimes it is also a good idea to use the different Designer tools in SQL to help us do the work without coding (so much)
Create a new Database
It is quite simple to create a new database in Microsoft SQL Server. Just right-click on the “Databases” node and select “New Database…”
There are lots of settings you may set regarding your database, but the only information you must fill in is the name of your database:
You may also use the SQL language to create a new database. The following script can be used to create a new database:
USE [Master] GO CREATE DATABASE SQLClass3 GO
In order to make a new SQL query, select the “New Query” button from the Toolbar. Here we can write any kind of queries that is supported by the SQL language.
● 1. Use the Management Studio to view all of the databases that are available from the server. If the Adventureworks database isn’t available, attach it to the server. Then, view the tables that are available from the AdventureWorks database. Finally, view the columns that are available from the customerAddress table. Note the primary and foreign keys of this table and the definition for each column.
● 2. Right-click on the Customer table and select the Design command to display the Customer table in a Table tab. Review the properties for each column in this table. In particular, note that the CustomerID column is defined as an identity column.
● 3. Use the Management Studio to create a diagram for the AdventureWorks database. Add the Contact, ContactCreditCard, ContactType and VendorContact tables. Then, organize the tables and connecting lines in the diagram so they are easy to read. (Hint: You can use the Autosize Selected Tables button and the Arrange Tables button in the toolbar to help you do this.) Finally, review the information that’s contained in each table, note the primary key of each table, and try to identify the relationships between the tables.
● 4. Start a new query and use the Query Editor to enter
● Press F5 to execute the query and display the results. If an error is displayed, correct the problem before you continue. (Hint: If you get an error message that indicates that ‘Vendors’ isn’t a valid object, the AdventureWorks database isn’t the current database. To fix this error, select the AdventureWorks database from the Available Databases combo box in the SQL Editor toolbar.) Then, save the query with a name of VendorsActive and close it.
● 5. Open the query named VendorsActive that you saved in exercise 4. Then, click the Execute Query toolbar button to execute it. (Hint: You may need to select the AdventureWorks database from the Available Databases combo box.)