5. (3 pts) Write a SQL command that will insert a record into the “appts” table above for a student named “Kelly”, where the advisor is “JSR”, and the room number is 5.
6. (3 pts) Write a SQL command that will display the name of students that are associated with the advisor named “JSR”.
7. (7 pts) Use the FRIENDS table to answer the following questions.
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
--------------- ---------------- -------- -------- -- ------
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
PERKINS ALTON 911 555-3116 CA 95633
BOSS SIR 204 555-2345 CT 95633
a. Write a query that returns everyone in the database whose last name begins with M.
b. Write a query that returns everyone who lives in Illinois with a first name of AL.
c. Given two tables (PART1 and PART2) containing columns named PARTNO, how would you find out which part numbers are in both tables? Write the query.
Use the INTERSECT. Remember that INTERSECT returns rows common to both queries.
d. What shorthand could you use instead of WHERE a >= 10 AND a <=30?
e. What will this query return?
SELECT FIRSTNAME
FROM FRIENDS
WHERE FIRSTNAME = 'AL'
AND LASTNAME = 'BULHER';
f. Using the FRIENDS table, write a query that returns the following:
NAME ST
------------------- --
AL IL
g. Using the FRIENDS table, write a query that returns the following:
NAME PHONE
-------------------------- ------------
MERRICK, BUD 300-555-6666
MAST, JD 381-555-6767
BULHER, FERRIS 345-555-3223
5. INSERT INTO appts VALUES ('Kelly, 'JSR', 5);
6. SELECT student FROM appts WHERE advisor = 'JSR';
7. a) SELECT * FROM FRIENDS WHERE LASTNAME LIKE 'M%';
b) SELECT * FROM FRIENDS WHERE FIRSTNAME = 'AL' AND ST = 'IL';
f) SELECT FIRSTNAME, ST FROM FRIENDS WHERE LASTNAME = 'Bundy';
NOTE: As per Chegg policy, I am allowed to answer only 4 questions (including sub-parts) on a single post. I have gone ahead and answered 5. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.
5. (3 pts) Write a SQL command that will insert a record into the “appts” table...
WRITE A SQL QUERY SQL SERVER Write a SELECT statement that returns one column from the Customers table named FullName that joins the LastName and FirstName columns. -- Format this column with the last name, a comma, a space, and the first name like this: -- Doe, John -- Sort the result set by last name in ascending sequence. -- Return only the contacts whose last name begins with a letter from M to Z. -- ...