The following is the data in the tables: 1 2 3 4 5 STUDENT Table StudentID...

 

Transcribed Image Text:

The following is the data in the tables: 1 2 3 4 5 STUDENT Table StudentID FirstName LastName Adams Buchanan Carter Ford 6 7 8 The tables for this assignment have the following columns: 10 STUDENT (StudentID, FirstName, LastName. LockerID) LOCKER (LockerD, LoackerNumber Lackertxoa) 20 30 40 50 60 70 LOCKER Table LockerID LockerNumber LockerType 80 90 LockerID. John NULL James NULL James 10 Gerald 20 Herbert Hoover 30 John Kennedy 40 Theodore Roosevelt 50 Harry Truman 60 100 101 102 103 104 105 106 107 108 Full Full Half Full Full Half Full Full Half StudentID, in the STUDENT table and Locked in the LOCKER table are surrogate primary keys. Locked in the STUDENT table is a foreign key. These tables, keys, and data are used as the basis for the SQL statements you will create in the exercises that follow. Run the SQL that you create in your personal database in the AWS SQL Server. To do this work you will need to sign on to SQL Server using Azure Data Studio and launch a query window (select File->New Query or key in the shortcut CTRL-N). For each SQL statement you write, show the results based on these data. Use data types consistent with SQL Server. 3.1 Write and run the SQL statements necessary to create the tables and their referential integrity constraints. Populate the tables with the above data. Assumptions: StudentID, is a surrogate key starting at 1 and incrementing by 1. LockerID, is a surrogate key starting at 10 and incrementing by 10. 3.2 Write and run an SQL query that performs an INNER JOIN of the STUDENT and LOCKER tables. 3.3 Write and run an SQL query that performs a LEFT OUTER JOIN of the STUDENT and LOCKER tables. 3.4 Write and run an SQL query to that performs a RIGHT OUTER JOIN of the STUDENT and LOCKER tables. 3.5 Write and run an SQL query to that performs a FULL OUTER JOIN of the STUDENT and LOCKER tables. The following is the data in the tables: 1 2 3 4 5 STUDENT Table StudentID FirstName LastName Adams Buchanan Carter Ford 6 7 8 The tables for this assignment have the following columns: 10 STUDENT (StudentID, FirstName, LastName. LockerID) LOCKER (LockerD, LoackerNumber Lackertxoa) 20 30 40 50 60 70 LOCKER Table LockerID LockerNumber LockerType 80 90 LockerID. John NULL James NULL James 10 Gerald 20 Herbert Hoover 30 John Kennedy 40 Theodore Roosevelt 50 Harry Truman 60 100 101 102 103 104 105 106 107 108 Full Full Half Full Full Half Full Full Half StudentID, in the STUDENT table and Locked in the LOCKER table are surrogate primary keys. Locked in the STUDENT table is a foreign key. These tables, keys, and data are used as the basis for the SQL statements you will create in the exercises that follow. Run the SQL that you create in your personal database in the AWS SQL Server. To do this work you will need to sign on to SQL Server using Azure Data Studio and launch a query window (select File->New Query or key in the shortcut CTRL-N). For each SQL statement you write, show the results based on these data. Use data types consistent with SQL Server. 3.1 Write and run the SQL statements necessary to create the tables and their referential integrity constraints. Populate the tables with the above data. Assumptions: StudentID, is a surrogate key starting at 1 and incrementing by 1. LockerID, is a surrogate key starting at 10 and incrementing by 10. 3.2 Write and run an SQL query that performs an INNER JOIN of the STUDENT and LOCKER tables. 3.3 Write and run an SQL query that performs a LEFT OUTER JOIN of the STUDENT and LOCKER tables. 3.4 Write and run an SQL query to that performs a RIGHT OUTER JOIN of the STUDENT and LOCKER tables. 3.5 Write and run an SQL query to that performs a FULL OUTER JOIN of the STUDENT and LOCKER tables.

Answer rating: 100% (QA)

31 create table student studentID int IDENTITY11 PRIMARY KEY firstName varchar100 lastName varchar10... View the full answer