Lab 2: SQL
Budget: $10 – $30 USD
Before you start: Install SQL, Workbench, and text book files in your environment.
DB used for this activity is: ap
Connect to a DB server, then use the ap DB.
View the data in the vendors table. take a snapshot of the result (name: youName-Lab2a)
What is the primary key column in the vendors table?
Write a SELECT statement that returns three columns from the Vendors table:vendor_name, vendor_contact_last_name, and vendor_contact_first_name.Then, run this statement to make sure it works correctlySubmit the SQL code name it: youName-Lab2-SQL1)
Write a SELECT statement that returns these three columns from the Vendors table:vendor_name, The vendor_name column from the Vendors tabledefault_account, The default_account_number column from the Vendors tabledescription The account_description column from the General_Ledger_Accounts tableSort the result set by account_description and then by vendor_nameThis should return 122 rows. Submit the SQL code name it : youName-Lab2-SQL2)
Write an INSERT statement that adds this row to the Invoices table:
invoice_id: The next automatically generated ID
vendor_id: 32
invoice_number: AX-014-027
invoice_date: 8/1/2018
invoice_total: $434.58
payment_total: $0.00
credit_total: $0.00
terms_id: 2
invoice_due_date: 8/31/2018
payment_date: null
Submit the SQL code name it: youName-Lab2-SQL3)
Write an UPDATE statement that modifies the invoice you added in exercise 6. This statement should change the credit_total column so it’s 10% of the invoice_total column, and it should change the payment_total column so the sum of the payment_total and credit_total columns are equal to the invoice_total column.Submit the SQL code name it: youName-Lab2-SQL4)
Write a DELETE statement that deletes the row that you added to the Invoices table in exercise 6. When you execute this statement, it will produce an error since the invoice has related rows in the Invoice_Line_Items table. To fix that, precede the DELETE statement with another DELETE statement that deletes the line items for this invoice. (Remember that to code two or more statements in a script, you must end each statement with a semicolon.)
Submit the SQL code name it: youName-Lab2-SQL5)
DB used for this activity is: ap
Connect to a DB server, then use the ap DB.
View the data in the vendors table. take a snapshot of the result (name: youName-Lab2a)
What is the primary key column in the vendors table?
Write a SELECT statement that returns three columns from the Vendors table:vendor_name, vendor_contact_last_name, and vendor_contact_first_name.Then, run this statement to make sure it works correctlySubmit the SQL code name it: youName-Lab2-SQL1)
Write a SELECT statement that returns these three columns from the Vendors table:vendor_name, The vendor_name column from the Vendors tabledefault_account, The default_account_number column from the Vendors tabledescription The account_description column from the General_Ledger_Accounts tableSort the result set by account_description and then by vendor_nameThis should return 122 rows. Submit the SQL code name it : youName-Lab2-SQL2)
Write an INSERT statement that adds this row to the Invoices table:
invoice_id: The next automatically generated ID
vendor_id: 32
invoice_number: AX-014-027
invoice_date: 8/1/2018
invoice_total: $434.58
payment_total: $0.00
credit_total: $0.00
terms_id: 2
invoice_due_date: 8/31/2018
payment_date: null
Submit the SQL code name it: youName-Lab2-SQL3)
Write an UPDATE statement that modifies the invoice you added in exercise 6. This statement should change the credit_total column so it’s 10% of the invoice_total column, and it should change the payment_total column so the sum of the payment_total and credit_total columns are equal to the invoice_total column.Submit the SQL code name it: youName-Lab2-SQL4)
Write a DELETE statement that deletes the row that you added to the Invoices table in exercise 6. When you execute this statement, it will produce an error since the invoice has related rows in the Invoice_Line_Items table. To fix that, precede the DELETE statement with another DELETE statement that deletes the line items for this invoice. (Remember that to code two or more statements in a script, you must end each statement with a semicolon.)
Submit the SQL code name it: youName-Lab2-SQL5)