Answer Happy

Use notepad and SQL Server to execute the statements. SOL Exercise (MAKE SURE THAT ALL TABLES ARE LABELED AS Tablename_YourInitials) Assignment: You are to submit on D2L all SQL using notepad AND use SQL Server to execute the statements. A. Create, Insert, & Select statement exercise Sample Table: empinfo id first last age Title City state 11180 Thomas Johnson 57 Director Blairsville Georgia 11182 Melissa Jones 29 Programmer Helen Georgia 44233 Mary Ann Edwards 24 Programmer 11 Dahlonega Georgia 15601 Solomon Sharp 32 Developer Alpharetta Georgia 88322 Graves Dawson 53 Manager Auburn Alabama 33225 Jamie Weber 41 Programmer Birmingham Alabama 98926 Ann Mcintosh 25 Developer Lawrenceville Georgia 98927 Bob Williams 46 Programmer 10 Auburn Alabama Create and Insert the table above before completing the statements below: 1. Insert yourself in the database - make up the information 2. Display the first name and age for everyone that's in the table. 3. Display the first name, last name, and city for everyone that's not from Georgia. 4. Display all columns for everyone that is over 40 years old. 5. Display the first and last names for everyone whose last name ends in "son". 6. Display all columns for everyone whose first name contains "Ann". Update statement exercises After each update, do a select statement to verify your changes. 1. Jamie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams. 2. Solomon Sharp's birthday is today, add 1 to his age. 3. All "Programmer Ir titles are now promoted to "Programmer III". 4. All "Programmer" titles are now promoted to "Programmer ll". Delete statement exercises (Use the select statement to verify your delete): 1. Thomas Johnson just quit, remove her record from the table.
B. CREATE the tables shown below and INSERT the data: Customers table customerid firstname lastname city state C10101 John Gray Lynden Washington C10298 Brown Pinetop Arizona C10339 Anthony Sanchez Winslow Arizona C10438 Kevin Smith Durango Colorado C10449 Isabela Moore Yuma Arizona Leroy Orders table Order ID customerid order_date item quantity price Order001 C10298 30-Jun-2021 Tent 1 88.00 Orderooz C10339 27-Jul-2021 Umbrella 5 4.50 Order003 C10449 14-Aug-2021 Ski Poles 2 25.50 Order004 C10449 18-Dec-2021 Raft 1 58.00 Order005 C10438 02-Dec-2021 Pillow 1 8.50 Order006 C10101 02-Jan-2022 Lantern 1 16.00 Order007 C10438 18-Jan-2022 Tent 3 79.99 Order008 C10449 28-Feb-2022 Flashlight 4 4.50 Order009 C10101 08-Mar-2022 Sleeping Bag 2 88.70 Order010 C10101 01-Apr-2022 Ear Muffs 2 12.50 Select Exercises 1. Select all columns from the Orders AND Customers tables for whoever purchased a Tent. Display the orderid, customerid, lastname, state, item, and price for this customer. HINT: don't forget to include the check and you will need table name included for customerid 2. Display a list of all items purchased for customerid C10449. Display the orderid, customerid, lastname, firstname, item, and price for this customer. HINT: don't forget to include the check and you will need table name included for customerid 3. Select the orderid, lastname, firstname, order_date, and item values from the Orders table for any items in the item column that start with the letter "S. Aggregate function Exercises 1. Select the maximum price of any item ordered in the Orders table label the results as Max Price. Hint: Select the maximum price only. 2. Select the average price of all of the items ordered that were purchased in the month of Dec. Label the result as Dec Avg. Hint: use Month(expression). 3. What are the total number of orders from customer C10101? Label result as Total Orders. GROUP BY Exercises 1. How many people are in each unique state in the customers table? Select the state and display the number of people in each. Label result as Total per State. Hint: count is used to count rows in a column, sum works on numeric data only. 2. From the Orders table, select the item, maximum price, and minimum price for each specific item in the table. Make sure to label results. Hint: use group by correctly. 3. How many orders did each customer make? Use the Orders table. Select the customerid, number of orders they made, and the sum of their orders. 4. Display the total purchase amount for each item purchased by customerid C10449. Display the orderid, item, quantity, price, total for this customer. HINT: don't forget to include the check and you will need table name included for customerid AND make sure you do group by correctly.