For this assignment, run this on SQL Workbench: Querying SQL Servers 1
Use SQL to complete the following exercises. 1. Create a table named REP. The table has the same structure as the SALES_REP table shown in Figure 3-15 except the LAST_NAME column should use the VARCHAR data type and the COMMISSION and RATE columns should use the NUMERIC data type. Execute the command to describe the layout and characteristics of the REP table. 2. Add the following row to the REP table: rep ID: 35, first name: Fred; last name: Kiser; address: 427 Billings Dr.; city: Cody; state: WY; postal: 82414 ; cell phone: 3075556309; commission: 0.00; and rate: 0.05. Display the contents of the REP table. 3. Delete the REP table. 4. Run the script file for the KimTay Pet Supplies database to create the five tables and add records to the tables. Be sure to select the script file for the particular DBMS that you are using (MySQL, Oracle, or SQL Server). (Note: If you do not have the script files for this text, ask your instructor for assistance.) 5. Confirm that you have created the tables correctly by describing each table and comparing the results to Figures 315,339,341,343, and 3-45. 6. Confirm that you have added all data correctly by viewing the data in each table and comparing the results to Figure 21 in Module 2. Critical Thinking 1. Review the data for the ITEM table in Figure 2-1 in Module 2 and then review the data types used to create the ITEM table in Figure 3-34. Suggest alternate data types for the DESCRIPTION, ON_HAND, and STOREHOUSE fields and explain your recommendations. Your ansad Stadent Acoommodation databases and to insert daka into them Ktariny Pre supplies had Figure 3-39 shows the CREATE TABLE command for the CUSTOMER table. Notice that the FIRST_NAME and LAST_NAME columns are specified as NOT NULL. Additionally, the CUST_ID column is the table's primary key, indicating that the CUST_ID column is the unique identifier of rows in the table. With this column designated as the primary key, the DBMS rejects any attempt to store a customer ID that already exists in the table. FIGURE 3-39 CREATE TABLE command for the CUSTOMEH tabie After creating the CUSTOMER table, you can create another file containing the INSERT commands to add the customer rows to the table. When a script file contains more than one command, each command must end with a semicolon. Figure 3-40 show Figure 3-45 contains the CRI Notice the way that the primary key is defined command for the INVOICE LINE table. column. FIGURE 3-45 CREATE TABLE command for the INVOICE_LINE tabie This CREATE TABLE command, which uses the data definition features of SQL a table named SALES_REP. The table contains ten columns: REP_ID, FIRST_NAME NAYE, ADDRESS, CITY, STATE, POSTAL, CELL_PHONE, COMMISSION, and RAT REP ID colum Module 3 Figure 3-43 contains the CREATE TABLE command for the ITEM table. FIGURE 3-43 CREATE TABLE command for the ITEM table Figure 3-44 contains the INSERT commands to load data into the ITEM talk: Figures 3-41 through 3-46 show the scripts for the CREATE TABLE and INSERT commands for creating and inserting data into the INVOICES, ITEM, and INVOICE LINE tables in the KimTay Pet Supplies database. Figure 3-41 contains the CREATE TABLE command for the INVOICES table. FIGURE 3-41 CREATE TABLE command tor the INVUICES tavie Figure 3-42 contains the INSERT commands to load data into the INVOICES tabl Notice the way that dates are entered. SALES REP FIGURE 2-1 Sample data for KimTay Pet Supplies Database Design Fundamentals FIGURE 2-1 Sample data for KimTay Pet Supplies (Continued)
