assignment 2: Write your solutions in a Word file, type my name: Reem Ghousheh and course number: AIT 524 in that file, name it M4_Assignment_BasicSQL. For each problem: Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’ ” REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Assignment: 1. Display a list of all data contained in the BOOKS table. 2. List the title only of all books available in inventory, using the BOOKS table. 3. List the title and publication date for each book in the BOOKS table. Use the column heading “Publication Date” for the Pubdate field. 4. List the customer number for each customer in the CUSTOMERS table, along with the city and state in which the customer lives. 5. Create a list containing the publisher’s name, the person usually contacted, and the publisher’s telephone number. Rename the contact column “Contact Person” in the displayed results. (Hint: Use the PUBLISHER table.) 6. Determine which categories are represented in the current book inventory. List each category only once. 7. List the customer number from the ORDERS table for each customer who has placed an order with the bookstore. List each customer number only once. 8. Create a list of each book title stored in the BOOKS table and the category in which each book belongs. Reverse the sequence of the columns so that the category of each book is listed first. 9. Create a list of authors that displays the last name followed by the first name for each author. The last names and first names should be separated by a comma and a blank space. 10. List all information for each order item. Include an item total, which can be calculated by multiplying the Quantity and Paideach columns. Use a column alias for the calculated value to show the heading “Item Total” in the output. I have attached an example of what is needed, please dont copy!
assignment 3: Save all your solution screenshots in a Word file, type my name “REEM GHOUSHEH” and course number” AIT 524″ in that file, name it M5_1_Practice_Problems. Instructions: For each problem Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’ from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Practice Problems: Table Creation and Management Create a new table containing at least four columns of four different data types (CHAR, VARCHAR2, NUMBER, DATE). Name the table your_first_name_your_last_name (substitute your_first_name with your first name and your_last_name with your last name). Retrieve a list of tables in your schema to confirm that the table created in Problem 1 is stored in your schema. Obtain information about the table created in Problem 1. Display the structure of the table that includes column names, data types, and constraints. Add a column to the table created in Problem 1. Change the size of any column in the table created in Problem 1. Add a new constraint (UNIQUE, CHECK, etc.) to any column in the table created in Problem 1. Drop a column in the table created in Problem 1. Mark one of the columns in the table created in Problem 1 as unused. Verify that the column is no longer available. Rename the table created in Problem 1. Delete the table created in Problem 1 permanently so that it isn’t moved to the recycle bin. Practice Problems: Constraints Write an SQL code to create the VENDOR table. The information about the VENDOR table is provided below. When creating the table, make sure to include appropriate constraints defined in the description. Write an SQL code to create the PRODUCT table. The information about the PRODUCT table is provided below. When creating the table, make sure to include appropriate constraints defined in the description. Assuming that each product is manufactured by many vendors and each vendor makes many products, create a bridge table between VENDOR and PRODUCT. When creating the table, make sure to include appropriate constraints (primary key, foreign keys, etc.). Use the ALTER TABLE … ADD CONSTRAINT command to add the UNIQUE constraint to the PROD_DESC column in the PRODUCT table. Use the ALTER TABLE … ADD CONSTRAINT command to add the CHECK constraint to the PROD_DATE column to check that a product was added to inventory after January 01, 2017. I HAVE ATTACHED AN EXAMPLE OF WHAT IS NEEDED PLEASE DONT COPY AND USE IT FOR CLARITY.
Assignment4: Paste the solutions in a Word file, type my name” REEM GHOUSHEH” and course number”AIT 524″ in that file, name it M5_1_Assignment_Table_Creation_Constraints For each problem: Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’*REEM GHOUSHEH* from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Assignment: For the database you designed in the previous assignments, create all tables together with appropriate data types, relationships, and constraints (PK, FK, NOT NULL, CHECK, DEFAULT, etc.). Make sure to name the PK and FK constraints using the Oracle naming conventions. For each table, display its structure. Note: Include your ERD/EERD from your previous assignment. I HAVE ATTACHED AN EXAMPLE OF THIS ASSIGNMENT, PLEASE DONT COPY AND USE FOR CLARITY. .
Assignment5: Instructions: For each problem Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’ “REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Practice Problems: Joining Data from Multiple Tables Before starting these problems, update the JustLee Books database by executing the JLDB_Build_9.sql script, otherwise, your solutions might not work! Note: In this module, it is assumed that students already have JLDB installed so they need to run JLDB_Build_9.sql to add the three tables to the existing DB. If you do not have it installed, you need to run JLDB_Build_8.sql first and then JLDB_Build_9.sql. Write a SQL code to join two tables using the WHERE statement. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology. Repeat Problem 1 above but remove the WHERE statement. What happened? Why? Write an SQL query to join three related tables using JOIN … ON in the JustLee Books. Select four-five columns that you would like to display in the output (do not use SELECT *). Include one condition with a special comparison operator. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology. Write an SQL query to join three related tables using JOIN … USING in the JustLee Books. Select four-five columns that you would like to display in the output (do not use SELECT *). Include one condition with a special comparison operator and a logical operator. Do not use the same set of tables as before. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology. Write an SQL query to join two tables using JOIN…ON and JOIN…USING (use SELECT *). Did you receive the same output? Why? Write an SQL query that lists customer’s last name and first name if he/she does not have the same address and shipping street and whose orders include books that have the letter “a“ in the title. In addition, consider only the following authors: Peterson, Adams, and Austin. Explain why some customers’ names appear multiple times. Write an SQL query to create a list of employees and their managers. Use columns’ aliases to clearly identify mangers and employees in the output. Modify Problem 7 above to list all customers along with those who referred them (if any). Write an SQL query to display a list of books in the BOOKS table. If a book has been ordered by a customer, also list the corresponding order number and the state in which the customer resides. Use set operators UNION, INION ALL, INTERSECT, and MINUS to combine the results of two queries. Explain what queries are intended to do in complete, coherent sentences with no SQL terminology. Assignment Submission Save all your solution screenshots in a Word file, type My name”REEM GHOUSHEH” and course number”AIT 524″ in that file, name it M6_1_Practice_Problems. I ATTACHED AN EXAMPLE OF THE ASSIGNMENT
Assignment6: nstructions For each problem: Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’ “REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Assignment: For the database you designed in the previous assignments, complete the following problems: Write a SQL code to join two tables using the WHERE statement. Explain what the query is intended to do. Repeat problem 1 using the JOIN … USING keywords. Repeat problem 1 using the JOIN … ON keywords. Repeat problem 1 but add at least two conditions in the WHERE statement and use an arithmetic and a special operator. Use logical operators to combine multiple conditions. Explain what the query is intended to do. Repeat problem 2 but add two more conditions in the WHERE statement and use an arithmetic and a special operator that are different from the ones you used in the previous query. Use logical operators to combine multiple conditions. Explain what the query is intended to do. -9 Repeat problems 1-4 above to join three tables. 10. Write a SQL query that joins four tables using any type of join and uses both an arithmetic and special operator. Explain what the query is intended to do. SUBMISSION: Paste the solutions in a Word file, type your name”REEM GHOUSHEH” and course number”AIT 524″ in that file, name it M6_1_Assignment_Joining_Data. I ATTACHED AN EXAMPLE OF THE ASSIGNMENT
Assignment7: Instructions: For each problem Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’ “REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Practice Problems: Joining Data from Multiple Tables Before starting these problems, update the JustLee Books database by executing the JLDB_Build_10.sql script, otherwise your solutions might not work! Write an SQL query to display a text string ‘your_first_name your_last_name’ that represents your full name. Then, use the INSTR function to determine the position of the single blank space in the text string ‘your_first_name your_last_name’. Use the DUAL table. Use the SUBSTR function to extract a substring that represents your last name from the text string ‘your_first_name your_last_name’. Assume you know the number of characters you need to extract. Use the INSTR and SUBSTR functions to extract a substring that represents your first name from the text string ‘your_first_name your_last_name’. Assume you do not know the number of characters to extract. Use the INSTR, SUBSTR, and LENGTH functions to extract a substring that represents your last name from the text string ‘your_first_name your_last_name’. Assume you do not know the number of characters you need to extract. Retrieve a list of all book titles along with authors’ names displayed as a single string ‘Last name, First name’ using the CONCAT function. In addition, also display the percentage of markup for each book as a whole number followed by a percent sign (for example, .2793 = 28%). The percentage of markup should reflect the difference between the retail and cost amount as a percent of the cost. Using today’s date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age. Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text “NOT REFERRED” if the customer wasn’t referred to JustLee Books by another customer or “REFERRED” if the customer was referred. Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies. Management is proposing to increase the price of each book. The amount of the increase will be based on each book’s category, according to the following scale: Computer books, 10%; Fitness books, 15%; Self-Help books, 25%; all other categories, 3%. Create a list that displays each book’s title, category, current retail price, and revised retail price. The prices should be displayed with two decimal places. The column headings for the output should be as follows: Title, Category, Current Price, and Revised Price. Sort the results by category. If there’s more than one book in a category, a secondary sort should be performed on the book’s title. Write a complex SQL query that is based on three or more tables and includes multiple nested single-row functions. Explain in a complete, coherent sentence what they query is intended to do. Practice Problems: Group Functions Before starting these problems, update the JustLee Books database by executing the JLDB_Build_11.sql script, otherwise your solutions might not work! What’s the retail price of the most expensive book written by Sam Smith? Determine the total profit generated by sales per customer. Note: Quantity should be reflected in the total profit calculation. Modify problem 2 to include only those customers who live in FL. Modify problem 3 to display the results with profit greater then $50. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50. Assignment Submission Save all your solution screenshots in a Word file, type your name”REEM GHOUSHEH” and course number “AIT 524″ in that file, name it M6_2_Practice_Problems.
Assignment8: Instructions For each problem: Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’”REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Assignment: For the database you designed in the previous assignments, complete the following problems: 1 – 4. Write four SQL queries to join two or more tables where each query contains multiple nested single-row functions. Make sure to use different functions in each query. Explain what each query is intended to do. 5. Write a query which is based on a single table and includes the GROUP B statement. Explain what the query is supposed to do. 6. Repeat problem 5 but add the WHERE statement. Explain what the query is supposed to do. 7. Repeat problem 6 but add the HAVING statement. Explain what the query is supposed to do. 8. Write a query which is based on three tables and includes the GROUP BY, WHERE and HAVING statements. Explain what the query is supposed to do. 9. Write an SQL query that uses the GROUP BY ROLL statement. Explain what the query is supposed to do. 10. Write an SQL query that uses the GROUP BY CUBE statement. Explain what the query is supposed to do. . Note: Include your ERD/EERD from your previous assignment. Assignment Submission Paste the solutions in a Word file, type your name”REEM GHOUSHEH” and course number “AIT 524″ in that file, name it M6_2_Assignment_Functions. I ATTACHED AN EXAMPLE OF THE ASSIGNMENT.
Assignment9: Instructions: For each problem Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’”REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Practice Problems: Joining Data from Multiple Tables Before starting these problems, update the JustLee Books database by executing the JLDB_Build_12.sql script, otherwise your solutions might not work! List the book title and retail price for all books with a retail price lower than the average retail price of all books sold by JustLee Books. Determine which books cost less than the average cost of other books in the same category. Determine which orders were shipped to the same state as order 1014. Determine which orders had a higher total amount due than order 1008. Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books. List the title of all books in the same category as books previously purchased by customer 1007. Don’t include books this customer has already purchased. List the shipping city and state for the order that had the longest shipping delay. Determine which customers placed orders for the least expensive book (in terms of the regular retail price) carried by JustLee Books. Determine the number of different customers who have placed an order for books written or co-written by James Austin. Determine which books were published by the publisher of The Wok Way to Cook. Assignment Submission Save all your solution screenshots in a Word file, type your name”REEM GHOUSHEH” and course number”AIT 524″ in that file, name it M7_1_Practice_Problems. I ATTACHED AN EXAMPLE OF THE ASIGNMENT
Assignment 10: Instructions For each problem: Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’”REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Assignment: For the database you designed in the previous assignments, complete the following problems: Write an SQL query that uses a single-row subquery in a WHERE clause. Explain what the query is intended to do. Write an SQL query that uses a multiple-column subquery in a FROM clause. Explain what the query is intended to do. Write an SQL query that is based on multiple tables and uses a subquery with the GROUP BY statement and HAVING clause. Explain what the query is intended to do. Write an SQL query that is based on multiple tables and uses a multiple-row subquery in a WHERE clause. The subquery will include the GROUP BY statement and another multiple-row subquery in a HAVING clause. Explain what the query is intended to do. Write an SQL query that joins three tables and uses any type of a subquery. Explain what the query is intended to do. Write an SQL query that is based on multiple tables and uses the DECODE function. Explain what the query is intended to do. For Problems 7-10 below, consider the following business scenario: A university wants to keep track of bonuses given to the professors who mentor (supervise) junior faculty members. Data about all professors is available in the Faculty table (see below). In that table, the f_super column represents a faculty id of a mentor (if any). A separate Bonus table is needed to assign and keep track of bonuses. The Bonus table will have a default bonus of 1000. It will be updated once a year to add new mentors and to update bonuses for the existing ones. 7. Create the Faculty table and populate it with data using the script below: CREATE TABLE faculty (f_id NUMBER(6), f_last VARCHAR2(30) ,f_first VARCHAR2(30), f_mi CHAR(1), loc_id NUMBER(5), f_phone VARCHAR2(10), f_rank VARCHAR2(9), f_super NUMBER(6), CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id)); INSERT INTO faculty VALUES (1, ‘Marx’, ‘Teresa’, ‘J’, 9, ‘4075921695’, ‘Associate’, 4); INSERT INTO faculty VALUES (2, ‘Zhulin’, ‘Mark’, ‘M’, 10, ‘4073875682’, ‘Full’, NULL); INSERT INTO faculty VALUES (3, ‘Langley’, ‘Colin’, ‘A’, 12, ‘4075928719’, ‘Assistant’, 4); INSERT INTO faculty VALUES (4, ‘Brown’, ‘Jonnel’, ‘D’, 11, ‘4078101155’, ‘Full’, NULL); Check the result using the select * from faculty; command. 8. Create the Bonus table that consists of two columns: f_id (PK) and bonus. For the f_id column, use the same description as in the Faculty table. For the bonus column, use the NUMBER data type and the DEFAULT constraint to set the values for the bonus column to 1000 (bonus amount). Next, use a subquery to copy ids of mentors given in the Faculty table into the Bonus table. Check the result using the select * from bonus; command. 9. Add two new records to the Faculty table using the command below. These records represent new faculty who came to the university this year. INSERT INTO faculty VALUES (5, ‘Sealy’, ‘James’, ‘L’, 13, ‘4079817153’, ‘Associate’, 1); INSERT INTO faculty VALUES (6, ‘Smith’, ‘John’, ‘D’, 10, ‘4238102345’, ‘Full’, NULL); Check the result using the select * from faculty; command. 10. Assume that the same Bonus table is used next year to assign and update bonuses. Use the MERGE statement to modify the Bonus table as follows: – if a mentor already exists in the Bonus table, increase the bonus by 1% – If there is a new mentor in the Faculty table, add him/her to the BONUS table Check the result using the select * from bonus; command. Note: Include your ERD/EERD from your previous assignment. Assignment Submission Paste the solutions in a Word file, type your name”REEM GHOUSHEH” and course number “AIT 524” in that file, name it M7_1_Assignment_Subqueires. I ATTACHED AN EXAMPLE OF THE ASSIGNMENT
Assignment 11: Instructions: For each problem Write and execute an SQL query in Oracle Live SQL or SQL*Plus Execute the following command: select sysdate, ‘your name’ “REEM GHOUSHEH” from dual; where your_name is substituted with your name Take a screenshot that includes both SQL statements and all results Copy and paste the screenshot into a Word file containing your solutions Practice Problems: Views Before starting these problems, update the JustLee Books database by executing the JLDB_Build_10.sql script, otherwise your solutions might not work! Create a view that lists the name and phone number of the contact person for each publisher. Don’t include the publisher’s ID in the view. Name the view CONTACT. Change the CONTACT view so that no users can accidentally perform DML operations on the view. Create a view called HOMEWORK13 that includes the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make sure the view is created even if the FIRSTATTEMPT table doesn’t exist. Attempt to view the structure of the HOMEWORK13 view. Create a view that lists the ISBN and title for each book in inventory along with the name and phone number of the person to contact if the book needs to be reordered. Name the view REORDERINFO. Try to change the name of a contact person in the REORDERINFO view to your name. Was an error message displayed when performing this step? If so, what was the cause of the error message? Select one of the books in the REORDERINFO view and try to change it’s ISBN. Was an error message displayed when performing this step? If so, what was the cause of the error message? Delete the record in the REORDERINFO view containing your name (If you weren’t able to perform #6 successfully, delete one of the contacts already listed in the table.) Was an error message displayed when performing this step? If so, what was the cause of the error message? Issue a rollback command to undo any changes made with the preceding DML operations. Delete the REORDERINFO view. Practice Problems: Additional Database Objects Before starting these problems, update the JustLee Books database by executing the JLDB_Build_5.sql script, otherwise, your solutions might not work! Create a sequence for populating the Customer# column of the CUSTOMERS table. When setting the start and increment values, keep in mind that data already exists in this table. The options should be set to not cycle the values and not cache any values, and no minimum or maximum values should be declared. Add a new customer row by using the sequence created in Question 1. The only data currently available for the customer is as follows: last name = Shoulders, first name = Frank, and zip = 23567. Create a sequence that generates integers starting with the value 5. Each value should be three less than the previous value generated. The lowest possible value should be 0, and the sequence shouldn’t be allowed to cycle. Name the sequence MY_FIRST_SEQ. Issue a SELECT statement that displays NEXTVAL for MY_FIRST_SEQ three times. Because the value isn’t being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the third SELECT? Change the setting of MY_FIRST_SEQ so that the minimum value that can be generated is –1000. Assignment Submission Save all your solution screenshots in a Word file, type your name and course number in that file, name it M7_2_Practice_Problems. I ATTACHED AN EXAMPLE OF THE ASSIGNMENT
