SQL Practice Exercise

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

  1. 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

  • 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)


  1. The column with underline is the primary key of the table.
  2. 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.
  3. 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.

You may also like...