Reference no: EM131864

Problem 1- Customers with certain list patterns

 

Problem description

 

• You are provided with four tables in Order_Supplier schema in the following screen. The table details are:
• Customer (cust_id, Fname, Lname, phonenumber, city) - contains information about the customers
• OrderDetails (order_ID, order_Date, cust_id, quantity, item_ID) - has details about the orders placed
• supplier (supplierID, supplierName, City, email, Phone) - gives all the information about the suppliers
• Item(itemID, itemName, supplier_ID, price, minQTY, maxQTY) - consists of details about the items
• You are required to write an SQL Query based on the tables given.

 

 

 

 

Tasks

 

You are supposed to write an SQL Query to display the details of all the customers, who do not have the alphabet e in their LNAME.

 

Note 1: The column(s) to be displayed in the result or output should be cust_id, Fname, Lname,  phonenumber, and city only.

 

Note2: While writing the SQL query, you are required to write the table name as schemaname.tablename (For example employee.projectDetails where employee is schemaname and projectDetails is tablename).

 

 

Problem 2- Employees without joining date details

 

Problem description

 

• You are provided with two tables in EMP Schema in the following screen. The table details are:
• Employee (EmpNo, Name, DOJ) - contains information about each employee
• EmployeeProject(EmpNo, Projectid, StartDate, Location, TotalHoursOnProject) - has details about the projects that each employee is working on. Note: Default date format is dd-mm-yyyy.
• You are required to write an SQL Query based on the tables given.

 

 

 

 

Tasks

 

You are required to write an SQL Query to fetch the names of all the employees whose date of joining is not known.

 

Note 1:  The column(s) to be displayed in the result or output should be 'Name' only.

 

Problem 3- Author's of a book

 

Problem description

 

• You are provided with three tables in LMS schema in the following screen. The table details are:
• Book (Title, Author, Publisher) - holds information about books.
• Library(BranchCode, Librarian, Address) - contains details of the libraries
• Book_library(BranchCode, Title, Copies) - has information about the books in all the libraries
• You are required to write an SQL Query based on the tables given.

 

 

 

Tasks

 

You are required to write an SQL query to get the author of the book "The Alchemist"

 

Note 1: The column(s) to be displayed in the result or output should be Author only.

 

Note 2: While writing the SQL query, you are required to write the table name as schemaname.tablename (For example employee.projectDetails where employee is schemaname and projectDetails is tablename).

 

 

Problem 4- Total Number of Tests

 

Problem description

 

• You are provided with Five tables in Assessments Schema in the following screen. The table details are:
• Questions (QuestionID, QuestionDesc, Option1, Option2, Option3, Option4, Answer, DifficultyLevel, Marks, SubjectId, TopicId)
• Subjects (SubjectID, SubjectName)
• Topics (TopicID, TopicName, SubjectID)
• Tests (TestID, TestName, TestDuration)
• TestQuestions(TestID, QuestionID)
• The database schema called Assessments is used as part of a portal that conducts various tests.

 

a. Questions table contains the set of test questions. Questions are all multiple-choice, single answer.

 

    This table contains several fields:

 

    QuestionID: Unique to a question (primary key)

 

    QuestionDesc: Question description

 

    Option1: Answer option-1 of the multiple choices

 

    Option2: Answer option-2 of the multiple choices

 

    Option3: Answer option-3 of the multiple choices

 

    Option4: Answer option-4 of the multiple choices

 

    Answer: Correct Answer (contains 1/2/3/4)

 

    Difficulty Level: Contains one of the value 1/2/3/4/5 (1- Easiest, 5 - Hardest)

 

    Marks: Marks for the question

 

    SubjectID: Subject Id

 

    TopicID: Topic Id.

 

b. Subjects table contains the set of subjects. Each subject forms a unique row in the table.

 

c. Topics table contains the set of topics. Each topic forms a unique row in the table and is mapped uniquely to one SubjectID.

 

d. Tests table contains the set of tests in the portal. Each test forms a unique row in the Tests table.

 

e. TestQuestions: A test contains 1-N questions. Each question of the test forms a unique row in the TestQuestions table.

 

 

You are required to write an SQL Query based on the tables given.

 

 

Tasks

 

You are required to write a SQL query to get the total number of tests present in the test table

 

Note1: The columns to be displayed in the result or output should be total only

 

Note 2: While writing SQL query, you are required to write the tablename as schemaname.tablename( for example employee.projectDetails where employee is the schemaname and projectDetails is the tablename)

 

Problem 5- Topics under a subject

 

Problem description

 

The database schema called Assessments is used as part of a portal that conducts various tests.

 

a. Questions table contains the set of test questions. Questions are all multiple-choice, single answer.

 

    This table contains several fields:

 

    QuestionID: Unique to a question (primary key)

 

    QuestionDesc: Question description

 

    Option1: Answer option-1 of the multiple choices

 

    Option2: Answer option-2 of the multiple choices

 

    Option3: Answer option-3 of the multiple choices

 

    Option4: Answer option-4 of the multiple choices

 

    Answer: Correct Answer (contains 1/2/3/4)

 

    Difficulty Level: Contains one of the value 1/2/3/4/5 (1- Easiest, 5 - Hardest)

 

    Marks: Marks for the question

 

    SubjectID: Subject Id

 

    TopicID: Topic Id.

 

 

b. Subjects table contains the set of subjects. Each subject forms a unique row in the table.

 

c. Topics table contains the set of topics. Each topic forms a unique row in the table and is mapped uniquely to one SubjectID.

 

d. Tests table contains the set of tests in the portal. Each test forms a unique row in the Tests table.

 

e. TestQuestions: A test contains 1-N questions. Each question of the test forms a unique row in the TestQuestions table.

 

 

You are required to write an SQL Query based on the tables given.

 

 

Tasks

 

You are required to write an SQL query to get all the topics that come under the subject, RDBMS.

 

Note1: The column(s) to be displayed in the result or output should be  TopicName only.

 

Note2: While writing SQL query, you are required to write the table name as schemaname.tablename (For example employee.projectDetails where employee is schemaname and projectDetails is tablename).

 

Problem 6 - Program with minimum duration

 

Problem description

 

A TV network has several channels. Each channel is in a particular language. Each channel has a number and a name. There are programs in the channel everyday and each program is for a certain duration in minutes. There are different packages comprising of a set of channels. A channel can be in one or more package. Each package has a cost in Indian Rupees.

 

 

• Based on above description, you are provided with four tables in TV_Cable Schema in the following screen. The table details are:
• Channels (ChannelNumber,ChannelName,Language)
• Programs (ProgramID,ProgramName,Channelnumber,Duration,Category)
• Packages (PackageID, PackageName, Cost)
• Package_channel(PackageID,ChannelNumber)
• You are required to write an SQL Query based on the tables given.

 

You are required to write an SQL Query based on the tables given.

 

 

Tasks

 

You are reuqired to write an SQL query to get the name of the programs of minimum duration.

 

Note1: The column(s) to be displayed in the result or output should be  ProgramName  only.

 

Problem 7 - Old home policies

 

Problem description

 

An insurance firm issues policies across both life and non-life products. The non-life products include car and home policies. Auto policies in terms are categorized under comprehensive coverage, Third party, fire and accident coverages.

 

• The key tables in the schema include
•  Policy_Codes: Holds data on the policy type code and the policy type name. Each policy type name is associated with a unique policy code.
• Policy_Types: Has details on the various policies, their type  and policy value. Each policy has a type and an associated value.
• Car_Policies: Has details on the car policy type, manufacturer, model and car sale date.
• Car_Policy_Types: Has data on the various car policy types and their names. Each car policy type code is associated with a unique code.
• Home_Policies: Has details on the address and date the house was built. Every home policy is associated with an address and a date on which the house was built.
• Addresses: Has data on the individual addresses.
• Life_Policies: Has data on the medical evaluation and prescription for each life policy.
• By using the above tables, Write an sql statement to find what is the percentage of home policies which are from the state of Arizona out of all Home policies by Policy Value.

 

 

Tasks

 

 Write a sql statement to find what is the percentage of home policies which are from the state of Arizona out of all Home policies by Policy Value.

 

Note 1: The corresponding column in the result must be called PERCENTAGE.

 

Problem 8 - Order with most Items

 

Problem description

 

An online merchant stocks multiple products that he offers to customers. Once the customer makes an order, the order is processed and the products delivered in one or more shipments. The following are the tables used in the schema.

 

• In certain cases an order might be serviced through multiple shipments- with sub-order going from 1 to n indicating number of splits of the order
• Customers: Has details on the customer and method to contact the customer (mail, phone or phone message)
• Tracking_Order: Has details on the status of each order.
• Order_Status_Codes: Has a mapping between order status code and its description.
• Shipments: Has details of the shipment details for each order. An order might be serviced through multiple shipments.
• Order_Items: This has details on the constituents of each sub-order in terms of products and the number of units of each product.
• Products: This has details on each of the products, the product id and the product name.
• Invoices: Stores the invoice no, invoice date and details of the invoice.
• The number of units in an sub-order = (product 1*quantity of product 1) + (product 2*quantity of product 2) +  (product 3*quantity of product 3) etc.
• By using the above tables, write an SQL statement to identify the order(s) that contain the most number of items. (List order id and number of items).

 

Tasks

 

Write an SQL statement to identify order(s) that contain the most number of items. (List order id and number of items).

 

Note 1: The corresponding columns in the result must be called order_ID and NumberOfItems.

 

Problem 9 - Tracking Order

 

Problem description

 

An online merchant stocks multiple products that he offers to customers. Once the customer makes an order, the order is processed and the products delivered in one or more shipments. The following are the tables used in the schema.

 

• In certain cases an order might be serviced through multiple shipments- with sub-order going from 1 to n indicating number of splits of the order:
• Customers: Has details on the customer and method to contact the customer (mail, phone or phone message)
• Tracking_Order: Has details on the status of each order.
• Order_Status_Codes: Has a mapping between order status code and its description.
• Shipments: Has details of the shipment details for each order. An order might be serviced through multiple shipments.
• Order_Items: This has details on the constituents of each sub-order in terms of products and the number of units of each product.
• Products: This has details on each of the products, the product id and the product name.
• Invoices: Stores the invoice no, invoice date and details of the invoice.

 

The number of units in an sub-order = (product 1*quantity of product 1) + (product 2*quantity of product 2) +  (product 3*quantity of product 3) etc.

 

Tasks

 

Write an SQL statement to list each order_status_code with number of orders. (List order_status_code, number of orders)

 

Note1: The corresponding columns in the result must be called order_status_code and numberOfOrders.

 

Problem 10 - Branch Manager with maximum number of sales persons

 

Problem description

 

A car dealership in the Minneapolis-St Paul area of Minnesota, US has presence in multiple branch locations in the area. Each of the branch locations has a branch manager and a set of salespeople. A single location would have multiple vehicles. The dealership stocks cars from various leading manufacturers. The dealership also has data on the features available in each car. Some cars have multiple features, while other cars might not have any features.

 

The following are the tables in the schema:

 

• Branches: This table has data on the branch code, branch name and branch manager code at each location.
• Sales_People: This table has detail on the sales person code, branch location and sales person name and contact details. A single branch will have multiple sales people mapped to it.
• Car_Manufacturers: This table has detail on car manufacturers and relationships the dealership has with them.
• Vehicles:  This table has details on individual vehicles- manufacturer name and color
• Inventory: This table captures detail of the cars present in each location. The inventory_ID maps every vehicleID to a single branch location
• Actual_Car_Features: This table has details on multiple features mapped to a single vehicle. A car might have none, one or multiple features.
• By using the above tables, write an SQL statement to identify the branch manager who has the maximum number of salespersons reporting to him (Branch Manager, branch location, number of salespeople)

 

 

Tasks

 

Write an SQL statement to identify the branch manager who has the maximum number of salespersons reporting to him (Branch Manager, branch location, number of salespeople)

 

Note 1: The corresponding columns in the result must be called branch_location , branch_Manager_Code and numberofsalespeople.

 

Note 2: While writing SQL query, you are required to write the table name as schemaname.tablename (For example Dealership.Branches where Dealership is schemaname  and Branches is tablename).

 

By using the above tables, Write an SQL statement to list each order_status_code with number of orders. (List order_status_code, number of orders)