Category: Databases

M6.1 Practice Problems
Please select the link above to submit this week’s assignment.Instructions
Practice Problems Instructions:When working with a database, it is important to know what tables it contains. It is also essential to have information about the structure of each table, including column names, data types, constraints, etc. to retrieve data. This kind of information (and much more!) can be found in the data dictionary, which is a collection of read-only tables that contain metadata, or data about the database.See more on the data dictionary here.Use the following commands to obtain information about the tables in the database:select table_name from user_tables; /* to display a list of tables */
select * from all_tab_columns where table_name=’table_name’; /* to display the structure of a table */
select * from table_name; /* to display the content of a table */
In the commands above, substitute table_name with the actual name of the table. If ‘table_name’ is enclosed in single quotation marks, the name of the table must be entered in capital letters.Important Note:For the practice problems below, follow examples shown in the PowerPoint presentations and textbooks but do not copy their solutions unless it is the only possible answer to the problem.General Instructions:The practice problems are arranged in order of increasing difficulty – the last several problems might present the most challenge. Students are expected to work out and submit the solutions to at least 5 problems. If you have previous experience in SQL you can select the 5 most challenging problems though it is still recommended to complete all problems. This will help you be better prepared for the Midterm and Final Exams. Challenge yourself to complete all problems!Instructions: For each problemWrite 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: Joining Data from Multiple TablesBefore 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 your name and course number in that file, name it M6_1_Practice_Problems.Due Date
This assignment is due by Sunday, 11:59 pm ET.
M6.1 Assignment – Joining Data from Multiple Tables
Please select the link above to submit this week’s assignment.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
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.
Assignment Submission
Paste the solutions in a Word file, type your name and course number in that file, name it M6_1_Assignment_Joining_Data. Due Date
This assignment is due by Sunday, 11:59 pm ET.
M6.2 Practice Problems
Please select the link above to submit this week’s assignment.Instructions
Practice Problems Instructions:When working with a database, it is important to know what tables it contains. It is also essential to have information about the structure of each table, including column names, data types, constraints, etc. to retrieve data. This kind of information (and much more!) can be found in the data dictionary, which is a collection of read-only tables that contain metadata, or data about the database.See more on the data dictionary here.Use the following commands to obtain information about the tables in the database:select table_name from user_tables; /* to display a list of tables */
select * from all_tab_columns where table_name=’table_name’; /* to display the structure of a table */
select * from table_name; /* to display the content of a table */
In the commands above, substitute table_name with the actual name of the table. If ‘table_name’ is enclosed in single quotation marks, the name of the table must be entered in capital letters.Important Note:For the practice problems below, follow examples shown in the PowerPoint presentations and textbooks but do not copy their solutions unless it is the only possible answer to the problem.General Instructions:The practice problems are arranged in order of increasing difficulty – the last several problems in each section might present the most challenge. Students are expected to work out and submit the solutions to at least 5 problems in each section. If you have previous experience in SQL you can select the 5 most challenging problems in each section though it is still recommended to complete all problems. This will help you be better prepared for the Midterm and Final Exams. Challenge yourself to complete all problems!Instructions: For each problemWrite 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: Selecting Single-Row Functions
Before starting these problems, update the JustLee Books database by executing the JLDB_Build_10.sql script, otherwise your solutions might not work! The JLDB_Build_10.sql script will add new tables to the existing JustLee Books Database. This script does not re-create the original tables. If the JustLee Books Database does not exist in your schema, you also need to run the JLDB_Build_8.sql script to create it.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! The JLDB_Build_11.sql script will add new tables to the existing JustLee Books Database. This script does not re-create the original tables. If the JustLee Books Database does not exist in your schema, you also need to run the JLDB_Build_8.sql script to create it.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 than $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 and course number in that file, name it M6_2_Practice_Problems.Due Date
This assignment is due by Sunday, 11:59 pm ET.
M6.2 Assignment – Selected Single-Row Functions, Group Functions
Please select the link above to submit this week’s assignment.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
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
Due Date
This assignment is due by Sunday, 11:59 pm ET.

use the data contained within the attached Excel file, to create a Power BI data model using industry best practices to solve the question. Here are the specific details & requirements of the mission:
Please recreate the screenshotted legacy REPORT FOR REPLICATION from the Excel file in power bi desktop in a tabular form.
create Dax functions in power query editor to help create the report
send the work to me in .pbix file and also in a work document add explanation on the dax functions used in getting the table right

use the data contained within the attached Excel file, to create a Power BI data model using industry best practices to solve the question. Here are the specific details & requirements of the mission:
please find attached data in the excel file to create tabular report in power bi desktop
send the work to me in .pbix file and also in a work document add explanation on the dax functions used in getting the table right
when you create the tabular report these are the measures am looking up for: Month when customers made first purchase, customers, pct who returned within 90 days of first purchase(%), pct who returned with following 3 month (%)

The questions are in the attached file. There are 3 questions. The answers must be plagiarism free.

Improvement of the timeline by :
1- Showing the Progress for each project
2- Showing if there is a delay in each stage. Please work accordingly. I want this timeline to operate dynamically once I update the status
Our Stages for the Projects are:
Concept > FEED > Procurement > Construction
Please consider these stages

This project is worth 14 marks and will be distributed as the following:
oDesign a Conceptual Schema using ER modeling concepts, including (Entities, Relationships, Attributes, Participation (Total or Partial), and Cardinality). (3 marks)
oTables before Normalization. (2 marks)
oTables after Normalization Using mapping Algorithm. (3 marks)
oUse MySQL or any other DBMS to create the normalized tables and
populate your tables with at least 10 rows. (3.5 marks)
oExecute the requested sample queries. (2.5 marks)
Each student must submit one report about their chosen Project via the Blackboard (Email submission will not be accepted and will be awarded ZERO marks) containing the following:
a)ER Diagram.
b)All schemas before normalization.
c)All schemas after normalization.
d)All SQL statements of:
§Creating tables.
§Inserting data in tables.
e)All requested queries/results.
Screenshots from MySQL (or any other software you use) of all the tables after population and query results.
You are advised to make your work clear and well presented; marks may be reduced for poor presentation. This includes filling in your information on the cover page.
You MUST show all your work, and text must not be converted into an image unless specified otherwise by the question.
Late submission will result in ZERO marks being awarded.
The work should be your own. Copying from students or other resources will result in ZERO marks.
Project I
Database System for a car rental company
Consider the company requirements as follows:
A car rental company needs a comprehensive database system to streamline its operations. The system should enable customers to rent cars from multiple locations (Riyadh, Jeddah, Makkah, Dammam, Al-Khobar, Qasim, Tabuk, AL-Baha, Al-Jawf, Ha’il, Aseer, Jazan, Najran). Customers will be required to register by providing their full name, email, phone number, and date of birth. The company owns a fleet of cars, which contains important details such as the manufacturer, model, manufacturing year, license plate, and daily rental rate. Every rental transaction will involve the customer, the rented car, and the rental period (start and end dates). As the company operates in multiple cities and states, multiple rental locations are available, each with its name, street address, city, state, and postal code.
Customers can rent multiple cars, and each car can be rented by multiple customers. A specific rental location will be associated with each car. Moreover, each rental transaction will be linked to a specific rental location. To maintain data integrity, constraints such as unique customer and car IDs will be enforced. Rental transactions must be associated with both a customer and a car.
On the other hand, each car should be linked to a rental location. Reports on rental transactions will be generated, including customer information, location, and period. User roles will be implemented for customers and system administrators. Customers will be able to view available cars, rent cars, and access their rental history. Meanwhile, administrators will be able to manage car inventory, rental locations, and customer accounts.
The system will ensure that cars are available for rent at specific locations and track car availability to prevent overbooking. Rental costs will be calculated based on the rental rate per day. User authentication and authorization will be implemented to protect customer data and system operations.

Describe software packages which offer data Manipulation in a computer. Describe the process of data Manipulation. State the advantages of data Manipulation

Kindly, answer the attached assignment with 0% AI usage and 0% matches on safeassign. The languge uses in the assignement is ( SQL ) I choosed java in the option below because SQL is not on the list. The everything must be SQL thank you