SQL_Server_Review_Lab
docx
keyboard_arrow_up
School
Dallas Colleges *
*We aren’t endorsed by this school
Course
2335
Subject
Information Systems
Date
Jan 9, 2024
Type
docx
Pages
5
Uploaded by PresidentDiscoveryTurkey31
*SQL Server Review*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file.
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
1.
What does the acronym T-SQL stand for?
Transact-SQL
2.
What keyword in a SQL query do you use to extract data from a database table?
SELECT Statement
3.
What keyword in a SQL query do you use to modify data from a database table?
UPDATE Statement
4.
What keyword in a SQL query do you use to add data from a database table?
Insert into
5.
What is the difference between the following joins?
a.
Left Join - Is the same as Left Outer Join and it is when the result set contains all values in the left table matching values in the right table and if none matches in the right table, the values are null.
b.
Inner Join - The result set contains values existing in both tables.
c.
Right Join - Is the same as Right Outer Join and it is when the result set contains all values in the right table matching values in the left table and if non matches in the left table, the values are null.
6.
What is the difference between a table and a view?
A TABLE contains actual data made up of rows and columns, a VIEW is created from these tables known as the base table; A table does not provide security to the stored data. A VIEW on the other hand is a virtual table consisting of columns from one or more tables. It is a query stored as an object in the database and serves as a security mechanism by restricting access.
7.
What is the difference between a temporary and variable table?
Page 1
*SQL Server Review*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file.
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
A temporary table is real table where you can do things like CREATE INDEXES while variable table
can have indexes by using PRIMARY KEY or UNIQUE constraints. Table variables don't participate in transactions, logging or locking.
Temp tables might result in stored procedures being recompiled, but Table variables will not.
Example 1.0:
TableA
TableB
Field1
Field1
1
2
2
5
3
7
4
6
4
3
5
3
6
9
Write SQL queries below based on table layout in Example 1.0.
8.
Display data from TableA where the values are identical in TableB.
SELECT
Orders.OrderID, Customers.CustomerName
FROM
Orders
INNER
JOIN
Customers
ON
Orders.CustomerID = Customers.CustomerID;
9.
Display data from TableA where the values are not available in TableB.
10. Display data from TableB where the values are not available in TableA.
11. Display unique values from TableA.
Page 2
*SQL Server Review*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file.
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
12. Display the total number of records, per unique value, in TableA.
13. Display the unique value from TableB where it occurs more than once.
14. Display the greatest value from TableB.
15. Display the smallest value from TableA.
Example 1.0 complete. Next page will be used to test your abilities with data manipulation.
16. Write a SQL statement to create a variable called Variable1 that can handle the value such as “Welcome to planet earth”.
17. Write a SQL statement that constructs a table called Table1 with the following fields:
a.
Field1 – this field stores numbers such as 1, 2, 3 etc.
b.
Field2 – this field stores the date and time.
c.
Field3 – this field stores the text up to 500 characters.
18. Write a SQL statement that adds the following records to Table1:
Field1
Field2
Field3
34
1/19/2012 08:00 AM
Mars Saturn
65
2/15/2012 10:30 AM
Big Bright Sun
89
3/31/2012 09:15 PM
Red Hot Mercury
Page 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
*SQL Server Review*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file.
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
19. Write a SQL statement to change the value for Field3 in Table1 to the value stored in Variable1 (From question 16), on the record that is 34.
20. Write a SQL statement for record 89 to return the total number of characters for Field3.
21. Write a SQL statement for record 65 to return the first occurrence of a space in Field3.
22. Write a SQL statement for record 65 to return the value “Bright” from Field3.
23. Write a SQL statement for record 34 to return the day from the Field2.
24. Write a SQL statement for record 34 to return the first day of the month from the Field2.
25. Write a SQL statement for record 34 to return the previous end of the month from the Field2.
26. Write a SQL statement for record 34 to return the day of the week from the Field2.
27. Write a SQL statement for record 34 to return the date as CCYYMMDD from the Field2.
28. Write a SQL statement to add a new column, Field4 (data type can be of any preference), to Table1.
29. Write a SQL statement to remove record 65 from Table1.
30. Write a SQL statement to wipe out all records in Table1.
31. Write a SQL statement to remove Table1.
Page 4
*SQL Server Review*
Lab
All labs should be built in SQL Server Management Studio and saved as a .sql file.
All labs should include a word document showing screenshots of the codes and the associated result sets, etc., for all applicable questions.
32. Create a sql statement that returns the TerritoryName, SalesPerson (
LastName Only
) ship method, credit card type (
If no credit card, it should say cash
), OrderDate and TotalDue
for ALL Transactions in the NorthWest Territory. Page 5