Instructions: Using your favorite text editor (probably NotePad++)

Instructions: Using your favorite text editor (probably NotePad++) create the file: Test1.sql Place a heading at the beginning of the file that looks something like this:/*Test 1Digna PatelITSE 2309*/Use your own name of course and you have license to change format as you believe looks professional so long as the document clearly states what it is and who wrote it.All of the queries will be written into this file; do not open a different file for each query; do not zip the file. Pay attention to the required file name! (Computer scientists are very fussy about file names because misnaming a file will usually break the program.) Number each exercise clearly in a comment and skip space between them. End each statement with a semicolon (‘;’) When you finish the entire file should run as an SQL script. The easier it is to read the better grade it will receive.During the test you may use your textbook notes and computer resources. You may not use email or any other form of communicationUnless the exercise specifically calls for an implicit join use an explicit INNER JOIN for all exercises in this block… and do not default the terms INNER or OUTER as seen in the textbook. A query may or may not produce any output and still be correct.You may refresh your tables by running the create_ap.sql script before the test.*************************************Part One: (For part one you will use your default database; don’t create a new one.) Part one is weighted at 20%. Format counts! Write the SQL code to create *at least* two tables; however you might need more tables to accomplish thespecific task. Your tables will keep track of:Doctors; the properties of a doctor are: Doctor ID First Name Last Name Address PhonePatients; the properties of a patient are: Patient ID (probably an SSN?) First Name Last Name Address Phone Date of birthCause the following relationships to exist: A doctor has many patients; a patient must have exactly one doctor (primary). (One to many)Write the code to insert at least three records into each table.Use proper SQL programming style for table creation (e.g.: name all of your constraints and use semicolons.)Choose meaningful identifiers and reasonable data types. For example: a last name might allow 16 characters… perhaps; 50 is too many and two characters is too few.Constrain all last names against NULL. *********************************************************************Part 2 (For part two use the ap_DB database and the existing Vendors-Invoices-Terms schema.)Items 1 through 4 are weighted at 5% each.Write SQL code to accomplish the following tasks:1) Insert the following record into the vendors table:VendorID0VendorNamePoohVendorAddress1VendorAddress2 VendorCityFriscoVendorStateTXVendorZipCode76209VendorPhone1234VendorContactLNameOrphansVendorContactFNameParent ofDefaultTermsID0DefaultAccountNo1002) Write an SQL statement that would change the TermsID to 7 for all of the invoices for VendorID 115. (Don’t worry about the error message just write the code.)3) Now you can start worrying about the error message. Problem #2 produced an error… why was there an error (be specific!)/* Place your text inside a comment.*/4) Zip code 93711 has changed to 93710. Write a single SQL statement to update the Vendors table to reflect the change.Items 4 through 7 are weighted at 10% each.5) Write the SQL code to delete VendorID = 82 from the Vendor’s table; also delete any associated invoices. This will probably require two SQL statements in the correct order.6) Write an SQL SELECT query to show the name and city for Vendors in California who have a ‘559’ area code; sort the output by city.7) Write an SQL SELECT query that shows the vendorName vendorState and termsDueDays for vendors who have a termsDueDays greater than 30.Items 8 and 9 are weighted at 15% each.8) In SQL Server you turn off a constraint using: ALTER TABLE tableName NOCHECK CONSTRAINT constraintName;Predictably you turn it back on with: ALTER TABLE tableName CHECK CONSTRAINT constraintName;The issue is that the data that were entered while it was disabled are never ckecked.Consider the following SQL statement (this particular syntax is unique to SQL Server). ALTER TABLE invoiceLineItems WITH CHECKCHECK CONSTRAINT FK_Inv_Line;The first line says to change the table and to recheck existing data against the constraint named in the second line. I always break that one onto two lines only because it really looks weird when I write: …WITH CHECK CHECK… Paste that last SQL statement into a query window and attempt to run it… it will fail because there are invalid data in the table.Write an SQL statement that will find them all. (That means that you have to figure out what is wrong.)(HINT! I’m looking for an outer join to find invalid foreign keys.)9) Write an SQL statement that will list the vendorName invoiceID and invoiceSequence. The vendorName is in the vendors table and the invoiceSequence is in the invoiceLineItems table. The vendors and invoiceLineItems tables are related only through invoives. Show only the vendor names that participate in the vendor/invoice relationship and all of the rows from the invoiceLineItems table including any unmatched rows. (Hint: see textbook page 146.)

Order now