SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 346

&5($7( 7$%/( ,192,&(
,QYRLFH1XPEHU ,QW

127 18// ,'(17,7<

,QYRLFH'DWH 'DWH 127 18//

3D\PHQW7\SH &KDU 127 18// '()$8/7 &DVK

6XE7RWDO 1XPHULF 18//

6KLSSLQJ 1XPHULF 18//

7D[ 1XPHULF 18//

7RWDO 1XPHULF 18//

(PDLO$GGUHVV 9DU&KDU 127 18//

&21675$,17 ,192,&(B3. 35,0$5< .(< ,QYRLFH1XPEHU

&21675$,17 ,192,&(B&86720(5B). )25(,*1 .(< (PDLO$GGUHVV

5()(5(1&(6 &XVWRPHU (PDLO$GGUHVV
2183'$7(&$6&$'(
21 '(/(7( 12 $&7,21

&5($7( 7$%/( /,1(B,7(0
,QYRLFH1XPEHU ,QW

127 18//

/LQH1XPEHU ,QW 127 18//

4XDQWLW\ ,QW 127 18//

8QLW3ULFH 1XPHULF 127 18//

7RWDO 1XPHULF 18//

3URGXFW1XPEHU &KDU 127 18//

&21675$,17 /,1(B,7(0B3. 35,0$5< .(< ,QYRLFH1XPEHU /LQH1XPEHU

&21675$,17 /B,B,192,&(B). )25(,*1 .(< ,QYRLFH1XPEHU

5()(5(1&(6 ,192,&( ,QYRLFH1XPEHU
21,3'$7(12$&7,21
21 '(/(7( &$6&$'(

&21675$,17 /B,B352'8&7B). )25(,*1 .(< 3URGXFW1XPEHU

5()(5(1&(6 352'8&7 3URGXFW1XPEHU
2183'$7(&$6&$'(
21 '(/(7( 12 $&7,21

Figure 7-41
Continued

C. Write an SQL script named HSD-Create-Tables.sql based on Figure 7-41 to create the
tables and relationships for the HSD database. Save this script, and then execute the
script to create the HSD tables.

D. Write an SQL script named HSD-Insert-Data.sql based on Figure 7-42 to insert the data
for the HSD database. Save this script, and then execute the script to populate the HSD
tables.

Using the hSD database, create an SQL script named hSD- RQ-CH07.sql to answer
questions E to Q. you can also include your answer to part Q, but be sure to put it
in comment marks so that it is interpreted as a comment by the DBMS and cannot
actually be run!

E. Write SQL statements to list all columns for all tables.

F. Write an SQL statement to list LastName, FirstName, and Phone for all customers who
live in Dallas.

G. Write an SQL statement to list LastName, FirstName, and Phone for all customers who
live in Dallas and have a LastName that begins with the letter T.

h. Write an SQL statement to list the INVOICE InvoiceNumber for sales that include
the Heather Sweeney Seminar Live in Dallas on 25-OCT-09 - Video. Use a subquery.
(Hint: The correct solution uses three tables in the query because the question asks for
INVOICE.InvoiceNumber. Otherwise, there is a possible solution with only two tables in
the query. )