SQL Practice Exercise - Programmingempire

The following list presents a SQL Practice Exercise.

The following are some of the databases. For the purpose of running queries given below you may use any one of these databases. However, you can use any other database also.

Part 1 – SQL Practice Exercise

Institute Database:

The database has the following tables

Student (enrolment_no, student_name, address, contact_no, gender, emailed, course_id)

SemesterShiftSection(enrolment_no, sem_no, shift_name, section_no)

Course (course_id, course_name, duration, credits)

Subject(course_id, subject_id, subject_name, maximum_marks)

Result(enrolment_no, subject_id, marks1, marks2, marks3, finalmarks)

2. Employer Database:

Employee (employee_id, employee_name, address, gender, salary, supervisor_id, address, department_no)

Department (dept_no, dept_name, manager_id, mgr_joining_date)

Dept_Location (dept_id, location) of

Project (project_id, project_title, dept_no, proj_location)

Employee_Project (employee_id, project_id, hours_worked)

Perform following tasks

At first, create an E-R diagram from given entities and their attributes.
After that transform this E-R diagram into a Relational model with proper constraints.
Further, write queries to execute the following DDL commands:
- CREATE: Create the structure of a table with some of the columns
- ALTER: Change the size of a particular column. Add a new column to the existing table. Remove a column from the table.
- DROP: Destroy the table along with its data.
- Execute Selection and Projection queries on the Course table

Formulate queries to execute the following DML commands:
- INSERT: Insert five records in each table.
- UPDATE: Modify data in single and multiple columns in a table
- DELETE: Delete selective records as well as all records from a table.
Write queries to execute the following DML command:
- SELECT: Retrieve the entire data of the table.
- Retrieve the selective contents (based on provided conditions) from a table.
- Retrieve contents from a table based on various operators i.e. string operators, logical operators and conditional operators, Boolean operators.
- Sort the data in ascending and descending order in a table on the basis of one column or more than one column.
Create a table using the following integrity constraints: Primary Key, Unique Key, Not Null, Check, Default, Foreign Key
Write queries to execute the following Aggregate functions: Sum, Avg, Count, Minimum, and Maximum value of a numeric column of a table using an aggregate function.
Retrieve data from a table using alias names.
Also, Retrieve data of a table using nested queries.
Retrieve data from more than one table using inner join, left outer, right outer, and full outer joins.
Create a view from one table and more than one table.
Also, create an index on a column of a table.

Part 2 – SQL Practice Exercise

Assume that an insurance company has a database comprising of the following tables. The primary keys are underlined.

Person(person_id, pname, address, contact_no, location)

Vehicle(registration_no, vehicle_type, model_no, reg_year)

Driver(driver_id, person_id, registration_no)

Accident(report_no, accident_date, location, description)

Participated(driver_id, registration_no, report_no, location, claim_amount)

Create the above tables by properly specifying the primary key and the foreign key
Enter at least five tuples for each relation
Demonstrate how you can
- Update the claim amount for the car with a specific Reg_no, the accident with report number 20 to 35000.
- Add a new accident record to the database.
Find the total number of people who owned vehicles that were involved in an accident in 2021.
Find the number of accidents in which cars belonging to a specific model were involved

Part 3 – SQL Practice Exercise

Assume that a Library Management System has the following schema. Formulate the SQL queries for the specified operations given below.

Book(book_id, book_title, author, publisher, year_of_publication)

Member(member_id, member_name, contactno, emailid)

Membership(membership_id, member_id, start_date, duration)

Book_Issue(issue_id, issue_date, member_is, book_id, duration)

Create the tables with the appropriate integrity constraints
Insert around 5 records in each of the tables
Display all records for all tables
List all the members with their membership numbers
Write a query to list all the issues for the current date with member names and book titles
List the details of students who borrowed books whose author is Herbert Schildt
Display a count of how many books have been issued to each member
Display a list of books taken by a member with member_id=71688.
Delete the List of books details that are issued on the current date
Create a view that lists out the issue_id, issue_date, member_id, member_name, book_title

Practice these questions of SQL Practice Exercise

Create a database for an E-Commerce company comprising of the following tables.

Customer(cust_id, cname, contact_no, referral_id)

Order(order_id, cust_id, order_date, staff_id)

ItemsOrdered(order_id, orderline_no, item_id, no_of_items, item_cost, shipping_date)

Item(item_id, item_type, price)

SupportStaff(staff_id, staff_type, dept_no, salary, emp_name)

Notes:

The column with underline is the primary key of the table.
Each customer may be referred by another customer. In such a case, the referral_d is the cust_id of the customer who is referring.
The total cost for a particular order line = no_of_items * item_cost.

Part 4 – SQL Practice Exercise

Write queries for the following

Create all the above tables.
Insert at least five records.
Display all the rows and columns in the Customer table. Sort by customer name in reverse alphabetical order.
Display the item number and total cost for each order line. Name the calculated column as Total_Cost.
Display all the customer ids in the Order table. Remove duplicates.
Display the order number and customer id from the Order table. Display the result in the format. Customer <cust_id> ordered <order_id>
Display full details from the ItemsOrdered table where the item_id is between 1 and 30 (no> or< operators) OR the item_id is greater than 500 AND (second condition) the item_cost is not in the list 4000, 6000, 8000 OR the order_id is not equal to 200.
Display the customer name and order date for all orders.
Repeat query (6) but also display all customers who have never ordered anything.
Display the customer name and order date for all orders using the join query.
Write a query to display the customer name and order date for all orders using Join.
Display the customer id, order date, and shipping date for all orders where the shipping date is between three and six months after the order date.
Write a query to display the customer id and customer name as well as the referring customer id and name.
Display the customer name in upper case only and in lower case only.
Display the third to seventh characters in each customer name.

Part 1 – SQL Practice Exercise

Part 2 – SQL Practice Exercise

Part 3 – SQL Practice Exercise

Practice these questions of SQL Practice Exercise

Notes:

Part 4 – SQL Practice Exercise

Write queries for the following

You may also like...

Learning Simple Queries in SQL

SQL Queries – Basic to Advanced

Data Definition Language Commands in SQL