T-SQL & SQL Server DB
Practices Part 2
T-SQL Basic
Exercise 1 SQL Basic—DDL,DML .............................................................................................................................. 2 Exercise 2 Basic T-SQL Query ..................................................................................................................................... 5 Exercise 3 Advanced T-SQL Query .............................................................................................................................. 8
Exercise 1
SQL Basic—DDL,DML
Scenario
In this exercise, you will become familiar with the basic syntax of SQL language, learning how to use DDL to create database objects and how to use DML to create data and update data. Tasks 1. Open SQL Server Management Studio and Create TSQLPractice Management Studio Project Detailed Steps 1. From the Windows task bar, select Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. 2. When the Connect to Server dialog box appears, verify that Server type is set to Database Engine, Server name is set to localhost, and that Windows Authentication is selected as the authentication method. 3. Click Connect to open your SQL Server instance. 1. Click File | New | Project, and choose SQL Server Scripts in the New Project dialog, type the project name “TSQLPractice”, and then click OK button, then a new Management Studio project is created. 2. You should see the TSQLPractice solution in the Solution Explorer window on the right side of the Management Studio. There is no files or connection now. 1. In the Solution Explorer, right click on the Queries folder and select New Query, the Connect to Database Engine dialog will prompt, confirm your server name and authentication mode and then click Connect button to connect server. 2. Right click on the created Query and choose Rename menu to rename the query file as “CreateObjectScripts.sql”. 3. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE NewDB --The Database created before CREATE TABLE Student ( StudentID int identity primary key, StudentName varchar(10), ClassCode char(4) default '0801', StudentPhone varchar(20) ) CREATE VIEW vStudent AS SELECT StudentID,StudentName,StudentPhone FROM Student 4. Press F5 or click the Execute button on the toolbar to execute the query. 5. Expand NewDB database and Table folder, you will see the new Student table; and expand the View folder, you will see the new view vStudent 1. In the Solution Explorer, create another Query file and name it “DataInsert.sql”. 2. Double-click the file and open the Query Editor window, In the query window, enter the following code: 2. Create new Query to create database objects 3. Insert data to Student table
Tasks Detailed Steps USE NewDB --The Database created before INSERT INTO Student(StudentName) VALUES('Peter') INSERT INTO Student(StudentName) VALUES('Jane') INSERT INTO Student(StudentName,StudentPhone) VALUES('Mel','12345678') INSERT INTO Student(StudentName,StudentPhone) VALUES('Yanny','13345678') INSERT INTO Student(StudentName,ClassCode,StudentPhone) VALUES('Jack','0802','22345678') INSERT INTO Student(StudentName,ClassCode,StudentPhone) VALUES('Tom','0802','23345678') INSERT INTO Student(StudentName,ClassCode,StudentPhone) VALUES('Richard','0802','24345678') INSERT INTO Student(StudentName,ClassCode,StudentPhone) VALUES('Joe','0802','25345678') INSERT INTO Student(StudentName,ClassCode,StudentPhone) VALUES('Mike','0802','26345678') 3. Press F5 or click the Execute button on the toolbar to execute the query. 4. Open the Student table in Object Explorer and you will all the data of Student table. 5. Also, you can explore the vStudent view data via right clicking on the view and choose “Open View” menu in the Object Explorer 1. In the Solution Explorer, create another Query file and name it “DataUpdate.sql”. 2. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE NewDB --The Database created before UPDATE Student SET StudentPhone='11345678' WHERE StudentName='Peter' UPDATE Student SET ClassCode='0802' WHERE StudentName='Mel' 3. Press F5 or click the Execute button on the toolbar to execute the query. 4. Open the Student table in Object Explorer to see the data change of Student table. 1. In the Solution Explorer, create another Query file and name it “DataDelete.sql”. 2. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE NewDB --The Database created before DELETE FROM Student WHERE StudentName='Mel' 4. Update Student table 5. Delete Data from Student table
Tasks Detailed Steps DELETE Student WHERE StudentName='Peter' 3. Press F5 or click the Execute button on the toolbar to execute the query. Open the Student table in Object Explorer to see the deletion of data .
Exercise 2
Basic T-SQL Query
Scenario
In this exercise, you will get data from the tables via SQL SELECT clause. From this part, you will learn how to specify the columns, filter rows, and rename columns’ name and sort the result. Tasks 1. Specifying Columns, Rename Columns’ title, Generate Calculated Columns Detailed Steps 1. In the Solution Explorer, create a new Query file and name it “BasicSQLQuery.sql”. 2. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE northwind --All columns SELECT * FROM dbo.Customers --Specific colums SELECT CustomerID, CompanyName, ContactName, ContactTitle,Phone FROM dbo.Customers --Rename column title SELECT CustomerID Customer, 'Customer Company Name'=CompanyName, Phone AS 'Customer Telephone' FROM dbo.Customers --Calculated Columns SELECT Employeeid ,Firstname+' '+Lastname AS FullName ,DATEDIFF(year,HireDate,GETDATE()) AS WorkingAge ,DATEDIFF(year,BirthDate,GETDATE()) AS Age FROM employees --Calculated Columns SELECT OrderID, ProductID ,(UnitPrice * Quantity) AS ExtendedAmount FROM [Order Details] 3. Press F5 or click the Execute button on the toolbar to execute the query. 4. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE northwind --Lists Only the First n Rows of a Result Set SELECT TOP 100 OrderID, ProductID ,UnitPrice, Quantity FROM [Order Details] --Lists First x percent Rows of a Result Set SELECT TOP 5 percent OrderID, ProductID ,UnitPrice, Quantity FROM [Order Details] 2. Filtering Data Using TOP Using Distinct Using Where Clause
Tasks Detailed Steps --Eliminating Duplicate Rows SELECT DISTINCT country FROM suppliers --Eliminating Duplicate Rows With Top N SELECT DISTINCT TOP 5 country FROM suppliers --Using the WHERE Clause to Specify Rows SELECT EmployeeID, LastName, FirstName, Country FROM employees WHERE country = 'USA' --Using the WHERE Clause to Specify Rows SELECT employeeid, lastname, firstname, title FROM employees WHERE employeeid = 5 Press F5 or click the Execute button on the toolbar to execute each query. 5. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE northwind --List all the American employees --who has worked for equal or more than 15 years SELECT EmployeeID, FirstName, Title , HireDate, Country FROM Employees WHERE DATEDIFF(year,HireDate,GETDATE())>=15 AND Country = 'USA' --List all the American employees --or employee who has worked more than 10 years, --and his age is more than 45 SELECT EmployeeID, FirstName, Title , HireDate, Country FROM Employees WHERE (DATEDIFF(year,HireDate,GETDATE())>=10 AND DATEDIFF(year,BirthDate,GETDATE())>45) OR Country = 'USA' --NULL value handling SELECT CustomerID, CompanyName , Country, Phone, Fax FROM dbo.Customers WHERE Fax IS NULL --Range Searching--Between...And SELECT OrderID, CustomerID, EmployeeID , OrderDate, RequiredDate, Freight FROM dbo.Orders WHERE RequiredDate BETWEEN '1996-1-1' AND '1996-12-31' --Range Searching--Between...And SELECT OrderID, CustomerID, EmployeeID , OrderDate, RequiredDate, Freight FROM dbo.Orders WHERE Freight BETWEEN 101 AND 200 --Range Searching--IN SELECT OrderID, CustomerID, EmployeeID 3. Filtering Data Using Operator Range Searching Fuzzy Searching
Tasks Detailed Steps , OrderDate, RequiredDate, Freight , ShipCountry FROM dbo.Orders WHERE ShipCountry IN ('UK','USA') --Fuzzy Searching SELECT CustomerID, CompanyName , Country, Phone, Fax FROM dbo.Customers WHERE Companyname LIKE '%Shop%' --Fuzzy Searching SELECT CustomerID, CompanyName , Country, Phone, Fax FROM dbo.Customers WHERE Companyname LIKE '_A%' Press F5 or click the Execute button on the toolbar to execute each query. 5. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE northwind --Sorting Data SELECT TOP 10 OrderID, CustomerID, EmployeeID , OrderDate, RequiredDate, Freight , ShipCountry FROM dbo.Orders ORDER BY Freight DESC --Sorting Data SELECT TOP 10 OrderID, CustomerID, EmployeeID , OrderDate, RequiredDate, Freight , ShipCountry FROM dbo.Orders ORDER BY Freight ASC --Sorting Data SELECT OrderID, CustomerID, EmployeeID , OrderDate, RequiredDate, Freight , ShipCountry FROM dbo.Orders ORDER BY ShipCountry ASC, Freight DESC --Sorting Calculated Columns SELECT Employeeid ,Firstname+' '+Lastname AS FullName ,DATEDIFF(year,HireDate,GETDATE()) AS WorkingAge ,DATEDIFF(year,BirthDate,GETDATE()) AS Age FROM employees ORDER BY WorkingAge DESC, Age DESC Press F5 or click the Execute button on the toolbar to execute each query. 4. Sorting Data
Exercise 3
Advanced T-SQL Query
Scenario
In this exercise, you will learn more about SQL syntax, get familiar with Group By, Join and Sub Query functions. .
1. Using Aggregate Functions (Count, Sum, Avg, Max, Min) 1. In the Solution Explorer, create a new Query file and name it “AdvancedSQLQuery.sql”. 2. Double-click the file and open the Query Editor window, In the query window, enter the following code: --Using Aggregate Functions SELECT COUNT(ProductID) AS 'Product Amount' ,AVG(UnitPrice) AS 'Average Price' ,MAX(UnitPrice) AS 'Highest Price' ,MIN(UnitPrice) AS 'Lowest Price' FROM dbo.Products --Using Aggregate Functions SELECT SUM(Freight) 'Total Freight' ,COUNT(DISTINCT CustomerID) 'Total Customer Amount' ,SUM(Freight)/COUNT(DISTINCT CustomerID) 'Avg Freight' FROM dbo.Orders 3. Press F5 or click the Execute button on the toolbar to execute the query. 4. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE northwind --GROUP BY SELECT CustomerID, SUM(Freight) AS 'Total Freight', COUNT(*) AS 'Times', AVG(Freight) AS 'Average Freight' FROM dbo.Orders GROUP BY CustomerID ORDER BY 'Total Freight' DESC --GROUP BY WITH WHERE Filter SELECT CustomerID, SUM(Freight) AS 'Total Freight', COUNT(*) AS 'Times', AVG(Freight) AS 'Average Freight' FROM dbo.Orders WHERE OrderDate>='1998-1-1' GROUP BY CustomerID ORDER BY 'Total Freight' DESC --GROUP BY WITH HAVING Filter SELECT CustomerID, SUM(Freight) AS 'Total Freight', COUNT(*) AS 'Times', AVG(Freight) AS 'Average Freight' FROM dbo.Orders GROUP BY CustomerID HAVING SUM(Freight)>1000 2. Grouping and Summarizing Data
T-SQL & SQL Server DB Practice Part2
ORDER BY 'Total Freight' DESC 5. Press F5 or click the Execute button on the toolbar to execute the query. 3. Joining Multiple tables 6. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE northwind --INNER JOIN SELECT a.OrderID, CustomerID, ProductID , Freight,UnitPrice, Quantity, Discount FROM dbo.Orders a INNER JOIN dbo.[Order Details] b ON a.OrderID=b.OrderID SELECT a.OrderID, CustomerID, ProductID , Freight,UnitPrice, Quantity, Discount FROM dbo.Orders a JOIN dbo.[Order Details] b ON a.OrderID=b.OrderID SELECT a.OrderID, CustomerID, ProductID , Freight,UnitPrice, Quantity, Discount FROM dbo.Orders a , dbo.[Order Details] b WHERE a.OrderID=b.OrderID --LEFT OUTER JOIN SELECT c.CustomerID, c.CompanyName , r.CustomerID 'CustomerID with Orders' FROM dbo.Customers c LEFT OUTER JOIN (SELECT DISTINCT CustomerID FROM dbo.Orders WHERE OrderDate>'1998-1-1') r ON c.CustomerID=r.CustomerID ORDER BY 'CustomerID with Orders' --Joining More Than Two Tables SELECT a.OrderID , c.CompanyName , e.FirstName+' '+e.LastName AS 'Employee' , Freight,UnitPrice, Quantity, Discount FROM dbo.Orders a INNER JOIN dbo.[Order Details] b ON a.OrderID=b.OrderID INNER JOIN dbo.Customers c ON a.CustomerID=c.CustomerID INNER JOIN dbo.Employees e ON a.EmployeeID=e.EmployeeID 7. Press F5 or click the Execute button on the toolbar to execute the query. 4. Using Sub Query 5. Double-click the file and open the Query Editor window, In the query window, enter the following code: USE northwind --Using a Subquery as a Derived Table SELECT OrderYear,OrderMonth, SUM(Freight) AS 'Total Freight', Page 9 of 10
T-SQL & SQL Server DB Practice Part2
COUNT(CustomerID) AS 'Times' FROM (SELECT CustomerID, YEAR(OrderDate) OrderYear, MONTH(OrderDate) OrderMonth, Freight FROM dbo.Orders) so GROUP BY OrderYear,OrderMonth --Using a Subquery as an Expression SELECT OrderID, CustomerID, EmployeeID , OrderDate, RequiredDate, ShippedDate , Freight, ShipCountry FROM dbo.Orders WHERE Freight= (SELECT MAX(Freight) FROM dbo.Orders) --Using Sub Query to Mimick a JOIN Clause SELECT CustomerID, CompanyName, ContactName ,ContactTitle, Country, Phone, Fax FROM dbo.Customers c WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM dbo.Orders WHERE OrderDate>'1998-1-1') Press F5 or click the Execute button on the toolbar to execute the query.
Page 10 of 10
因篇幅问题不能全部显示，请点此查看更多更全内容