本文发表在 rolia.net 枫下论坛1. Describe the basic form of the MSSQL Select Statement:
2. Describe 2 types of ANSI JOINs:
3. Write this query as a subquery:
SELECT Employee_ID, LastName
FROM Employee JOIN publishers ON employee.pub_id=publishers.pub_id
WHERE pub_name='MSSQL 2000 Development'
4. Write a correlated subquery using any tables and explain it:
5. Write one query using an MSSQL aggregate function and describe it:
6. Describe the basic form of an index:
7. Create a stored procedure and explain what it does using a one line SQL comment:
8. Write a transaction and COMMIT it:
9. List 2 transaction isolation levels supported by MSSQL (list only 2):
10. Describe how you can create a deadlock condition between two processes.
11. Given the data below, write a query that will show the minimum Sample_Depth for each unique Location_ID.
Table: Sample_Table
Location_ID Hole_Number Location_Description Sample_Depth (meters)
1 1 Gulf of Mexico 200
1 2 Gulf of Mexico 282
1 3 Gulf of Mexico 143
2 4 Monterey Bay 100
2 5 Monterey Bay 114
3 6 Puget Sound 123
4 7 Straits of Gibraltar 100
5 8 Straits of Gibraltar 176
The result should return all columns from Sample_Table.
For example, for Location_ID = 1, the minimum
Sample_Depth is 143. The query should return that row. And, the query should
also return the rows with the lowest Sample_Depth for Location_ID 2, 3, 4 and 5.
12. In question 11, what column could you add to the Sample_Table to make the query
easier to write? Please rewrite the query using this column.
13. Please explain, in your own words, what DTS packages can be used for.
14. In question 11, if you had to import the Sample_Table data into a new table, what
steps would you take?
15. What prefix is added to a table's name to indicate that
it is a global temporary table?
16. Write a query using a self-join and explain the result set returned.
17. What are the advantages of using a Windows authenticated login versus
a MSSQL login account?
18. If you have a server with two MSSQL instances, and one instance
is rarely used, what settings would you change to insure the more heavily
used instance has more memory available to it?
19. What table hint could you use in a stored procedure's SELECT statement
that would be equivalent to the READ UNCOMMITTED (dirty read) isolation level?
20. Which of the following RAID levels provides the highest read/write performance?
Level 1, Level 2, Level 3, Level 4, Level 5 or Level 10 (1+0)
21. Please describe a star schema. Please include your own table names and relationships.
22. Please describe a snowflake schema. Please include your own tables names and relationships.
23. For the tables you created in 21. and 22. write at least 2 INSERT statements for each table.
In one INSERT statement, identify the column names.
In the second INSERT statement, INSERT the values without the column names.
24. Which schema type requires more joins?
25. Which schema type offers better performance?
26. Using SQL, ALTER one of your tables created in 21. and 22. so that there is a column constraint.
For example, if one of your columns was Price, alter it so that Price can only be greater than $19.95.
27. Write an INSERT statement to INSERT the date 3/7/2005 into a MSSQL date field.
Only insert the date portion.
28. Write a SELECT statement that would read that date value and format it Monday, March 7, 2005.
29. Given the following tables and columns in Table 1, fill in Table 2.
Table 1
Table Name Column Names
---------- ------------
Patient PatientID, PatientName
Treatment TreatmentID, DoctorID, TreatmentName, TreatmentDate
Doctor DoctorId, DoctorName
Admission AdmissionID, PatientID, TreatmentID, RoomID, AdmissionDate
Room RoomID, RoomNumber, RoomType
Table 2
Table Name Primary Key Foreign Key(s) Tables Referenced
---------- ----------- -------------- -----------------
Patient ? ? ?
Treatment ? ? ?
Doctor ? ? ?
Admission ? ? ?
Room ? ? ?
30. Write an ALTER statement to create the foreign key relationship between the table Patient
and the table Admission in question 29.
31. Come up with your own unique table and column names, like in question 29.
Include at least 4 tables. Fill in the following:
Table 1
Table Name Column Names
---------- ------------
Table 2
Table Name Primary Key Foreign Key(s) Tables Referenced
---------- ----------- -------------- -----------------
32. Using Microsoft Access, Microsoft Word or VISIO, create an ER diagram from the tables you created in question 31.更多精彩文章及讨论，请光临枫下论坛 rolia.net
2. Describe 2 types of ANSI JOINs:
3. Write this query as a subquery:
SELECT Employee_ID, LastName
FROM Employee JOIN publishers ON employee.pub_id=publishers.pub_id
WHERE pub_name='MSSQL 2000 Development'
4. Write a correlated subquery using any tables and explain it:
5. Write one query using an MSSQL aggregate function and describe it:
6. Describe the basic form of an index:
7. Create a stored procedure and explain what it does using a one line SQL comment:
8. Write a transaction and COMMIT it:
9. List 2 transaction isolation levels supported by MSSQL (list only 2):
10. Describe how you can create a deadlock condition between two processes.
11. Given the data below, write a query that will show the minimum Sample_Depth for each unique Location_ID.
Table: Sample_Table
Location_ID Hole_Number Location_Description Sample_Depth (meters)
1 1 Gulf of Mexico 200
1 2 Gulf of Mexico 282
1 3 Gulf of Mexico 143
2 4 Monterey Bay 100
2 5 Monterey Bay 114
3 6 Puget Sound 123
4 7 Straits of Gibraltar 100
5 8 Straits of Gibraltar 176
The result should return all columns from Sample_Table.
For example, for Location_ID = 1, the minimum
Sample_Depth is 143. The query should return that row. And, the query should
also return the rows with the lowest Sample_Depth for Location_ID 2, 3, 4 and 5.
12. In question 11, what column could you add to the Sample_Table to make the query
easier to write? Please rewrite the query using this column.
13. Please explain, in your own words, what DTS packages can be used for.
14. In question 11, if you had to import the Sample_Table data into a new table, what
steps would you take?
15. What prefix is added to a table's name to indicate that
it is a global temporary table?
16. Write a query using a self-join and explain the result set returned.
17. What are the advantages of using a Windows authenticated login versus
a MSSQL login account?
18. If you have a server with two MSSQL instances, and one instance
is rarely used, what settings would you change to insure the more heavily
used instance has more memory available to it?
19. What table hint could you use in a stored procedure's SELECT statement
that would be equivalent to the READ UNCOMMITTED (dirty read) isolation level?
20. Which of the following RAID levels provides the highest read/write performance?
Level 1, Level 2, Level 3, Level 4, Level 5 or Level 10 (1+0)
21. Please describe a star schema. Please include your own table names and relationships.
22. Please describe a snowflake schema. Please include your own tables names and relationships.
23. For the tables you created in 21. and 22. write at least 2 INSERT statements for each table.
In one INSERT statement, identify the column names.
In the second INSERT statement, INSERT the values without the column names.
24. Which schema type requires more joins?
25. Which schema type offers better performance?
26. Using SQL, ALTER one of your tables created in 21. and 22. so that there is a column constraint.
For example, if one of your columns was Price, alter it so that Price can only be greater than $19.95.
27. Write an INSERT statement to INSERT the date 3/7/2005 into a MSSQL date field.
Only insert the date portion.
28. Write a SELECT statement that would read that date value and format it Monday, March 7, 2005.
29. Given the following tables and columns in Table 1, fill in Table 2.
Table 1
Table Name Column Names
---------- ------------
Patient PatientID, PatientName
Treatment TreatmentID, DoctorID, TreatmentName, TreatmentDate
Doctor DoctorId, DoctorName
Admission AdmissionID, PatientID, TreatmentID, RoomID, AdmissionDate
Room RoomID, RoomNumber, RoomType
Table 2
Table Name Primary Key Foreign Key(s) Tables Referenced
---------- ----------- -------------- -----------------
Patient ? ? ?
Treatment ? ? ?
Doctor ? ? ?
Admission ? ? ?
Room ? ? ?
30. Write an ALTER statement to create the foreign key relationship between the table Patient
and the table Admission in question 29.
31. Come up with your own unique table and column names, like in question 29.
Include at least 4 tables. Fill in the following:
Table 1
Table Name Column Names
---------- ------------
Table 2
Table Name Primary Key Foreign Key(s) Tables Referenced
---------- ----------- -------------- -----------------
32. Using Microsoft Access, Microsoft Word or VISIO, create an ER diagram from the tables you created in question 31.更多精彩文章及讨论，请光临枫下论坛 rolia.net