Class 12 Computer Science (Python) – Chapter 7 Structured Query Language

Chapter Overview – Structured Query Language (SQL)

Chapter 7 introduces students to Structured Query Language (SQL), the standard language for managing and manipulating relational databases. SQL allows users to interact with a database by executing various commands to retrieve, insert, update, and delete data, as well as to define and modify database structures.

In this chapter, students will explore the fundamental SQL commands and their practical applications for database operations. The concepts of data manipulation, database structure management, and data retrieval are covered in detail, with a focus on writing efficient SQL queries and understanding the importance of data integrity and normalization.

By mastering SQL, students will gain essential skills for working with relational databases, which are widely used in industries such as software development, data analysis, and web development.


What You Will Learn in Chapter 7

This chapter equips students with the knowledge to interact with relational databases using SQL. They will understand how to perform data retrieval and manipulation through SQL queries, as well as how to structure and manage databases efficiently. SQL’s role in database administration is emphasized, with a focus on ensuring accurate data handling and maintaining the integrity of the database.

Key Topics Covered

  1. Introduction to SQL

    • SQL is a standard language used to interact with relational databases.

    • It allows for data querying, updating, and managing database structures.

  2. Types of SQL Commands

    • DDL (Data Definition Language): Used to define, modify, and delete database structures.

      • Examples: CREATE, ALTER, DROP

    • DML (Data Manipulation Language): Used to manipulate data within the tables.

      • Examples: SELECT, INSERT, UPDATE, DELETE

    • DCL (Data Control Language): Used to control access to data in the database.

      • Examples: GRANT, REVOKE

    • TCL (Transaction Control Language): Used to manage transactions.

      • Examples: COMMIT, ROLLBACK

  3. Basic SQL Commands

    • SELECT: Retrieving data from one or more tables.

    • INSERT: Adding new records to a table.

    • UPDATE: Modifying existing records in a table.

    • DELETE: Removing records from a table.

  4. Advanced SQL Queries

    • WHERE Clause: Filtering data based on conditions.

    • AND, OR Operators: Combining multiple conditions in queries.

    • ORDER BY Clause: Sorting query results.

    • JOIN Operations: Combining data from multiple tables.

    • GROUP BY Clause: Grouping rows based on column values.

  5. SQL Functions

    • Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()

    • String Functions: CONCAT(), UPPER(), LOWER(), LENGTH()

    • Date Functions: NOW(), CURDATE(), DATE_ADD()

  6. Normalization

    • Understanding the importance of database normalization to reduce redundancy and dependency.

    • Normal forms (1NF, 2NF, 3NF) and their applications.

Download Chapter 7 Solutions PDF – Structured Query Language

Our downloadable PDF includes:

  • NCERT textbook questions and answers.

  • Topic-wise concept explanations with examples.

  • SQL query syntax with sample queries.

  • Diagrams for JOIN operations and normalization.

  • Quick revision notes and exam pointers.

Highlights of Our NCERT Solutions

  • Clear explanations of SQL syntax and commands.

  • Step-by-step examples to demonstrate query writing.

  • Visual representations for table structures and joins.

  • Focus on exam-oriented short and long answers.

  • Practical examples using real-world database scenarios.

Recommended Preparation Tips

  • Understand the differences between DDL, DML, DCL, and TCL commands.

  • Practice writing SQL queries for common database tasks like SELECT, INSERT, and UPDATE.

  • Focus on using WHERE, JOIN, and GROUP BY clauses effectively.

  • Learn how to normalize tables and understand the different normal forms.

  • Review SQL functions and practice their applications in queries.

Additional Study Resources

  • Flashcards: SQL command types and syntax.

  • Visual guides for database relationships and normalization.

  • Sample SQL queries and answers from past CBSE papers.

  • Online quizzes on SQL query writing and database concepts.

Mastering Chapter 7 – Structured Query Language (SQL)

Mastering SQL is a key skill in database management and plays a pivotal role in both academic exams and real-world software development. This chapter provides a strong foundation for students who wish to pursue careers in database administration, web development, and data science.

A solid understanding of SQL commands, query writing, and database structure will not only help students score well in the CBSE Class 12 exams but also serve as a core skill in professional software and web development fields.

Class 12 Computer Science (Python) – Chapter 7 Structured Query Language

NCERT Textbook Questions Solved – Class 12 Computer Science (Python)

Short Answer Type Questions-I

Question 1:

Write queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.

Table: VEHICLE

+-------+------------+---------+---------+------+
| VCODE | VEHICLETYPE| PERKM   | KM      | NOP  |
+-------+------------+---------+---------+------+
| V01   | Car        | 10      | 50      | 3    |
| V02   | Bus        | 15      | 120     | 50   |
| V03   | Car        | 10      | 80      | 2    |
| V04   | Bus        | 15      | 150     | 60   |
| V05   | Truck      | 20      | 200     | 2    |
+-------+------------+---------+---------+------+

SQL Queries and Outputs:

  1. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO:
    SELECT CNO, CNAME, TRAVELDATE FROM TRAVEL ORDER BY CNO DESC;
  2. To display the CNAME of all customers from the table TRAVEL who are travelling by vehicle with code V01 or V02:
    SELECT CNAME FROM TRAVEL WHERE VCODE IN (\'V01\', \'V02\');
  3. To display the CNO and CNAME of those customers from the table TRAVEL who travelled between ‘2015-12-31’ and ‘2015-05-01’:
    SELECT CNO, CNAME FROM TRAVEL WHERE TRAVELDATE BETWEEN \'2015-05-01\' AND \'2015-12-31\';
  4. To display all the details from table TRAVEL for customers who have travelled a distance of more than 120 KM, in ascending order of NOP:
    SELECT * FROM TRAVEL WHERE KM > 120 ORDER BY NOP ASC;
  5. To count the number of records per vehicle code:
    SELECT COUNT(*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT(*) > 1;
  6. To display distinct vehicle codes from the TRAVEL table:
    SELECT DISTINCT VCODE FROM TRAVEL;
  7. To display vehicle code, customer name, and vehicle type for customers travelling less than 90 KM:
    SELECT A.VCODE, CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B WHERE A.VCODE = B.VCODE AND KM < 90;
  8. To display the customer name and total fare for customers who travelled in vehicle V05:
    SELECT CNAME, KM * PERKM FROM TRAVEL A, VEHICLE B WHERE A.VCODE = B.VCODE AND A.VCODE = \'V05\';

Question 2:

Consider the following tables SCHOOL and ADMIN and answer this question:

Table: SCHOOL
+------------+----------+------------+
| TEACHER   | DESIGNATION | EXPERIENCE |
+------------+----------+------------+
| Alice     | Teacher    | 5          |
| Bob       | Principal  | 15         |
| Charlie   | Teacher    | 10         |
+------------+----------+------------+

Table: ADMIN
+------------+------------+---------+
| TEACHER   | DESIGNATION | GENDER  |
+------------+------------+---------+
| Alice     | Teacher     | Female  |
| Bob       | Principal   | Male    |
| Charlie   | Teacher     | Male    |
+------------+------------+---------+

SQL Queries and Outputs:

  1. To display count of teachers by designation, where the count is less than 2:
    SELECT DESIGNATION, COUNT(*) FROM ADMIN GROUP BY DESIGNATION HAVING COUNT(*) < 2;
  2. To find the maximum experience from the SCHOOL table:
    SELECT MAX(EXPERIENCE) FROM SCHOOL;
  3. To display teachers with experience greater than 12, ordered by teacher name:
    SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE > 12 ORDER BY TEACHER;
  4. To count the number of teachers by gender:
    SELECT COUNT(*), GENDER FROM ADMIN GROUP BY GENDER;

Question 3:

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables TRANSPORT and TRIP.

Table: TRANSPORT
+-------+------------+---------+------+
| TCODE | TTYPE      | PERKM   | KM   |
+-------+------------+---------+------+
| 101   | Truck      | 10      | 50   |
| 102   | Bus        | 15      | 120  |
| 103   | Car        | 12      | 100  |
| 104   | Van        | 18      | 80   |
| 105   | Truck      | 20      | 200  |
+-------+------------+---------+------+

Table: TRIP
+-------+------------+------------+----------+------+
| NO    | NAME       | TCODE      | TDATE    | NOP  |
+-------+------------+------------+----------+------+
| 1     | John       | 101        | 2015-02-10 | 4   |
| 2     | Mark       | 103        | 2015-03-15 | 2   |
| 3     | Sam        | 102        | 2015-04-01 | 6   |
| 4     | Alex       | 104        | 2015-05-05 | 5   |
| 5     | Chris      | 105        | 2015-06-01 | 3   |
+-------+------------+------------+----------+------+

SQL Queries and Outputs:

  1. To display NO, NAME, TDATE from the table TRIP in descending order of NO:
    SELECT NO, NAME, TDATE FROM TRIP ORDER BY NO DESC;
  2. To display the NAME of drivers from the table TRIP who are traveling by transport vehicle with code 101 or 103:
    SELECT NAME FROM TRIP WHERE TCODE IN (101, 103);
  3. To display the NO and NAME of those drivers from the table TRIP who travelled between ‘2015-02-10’ and ‘2015-04-01’:
    SELECT NO, NAME FROM TRIP WHERE TDATE BETWEEN \'2015-02-10\' AND \'2015-04-01\';
  4. To display all the details from table TRIP for drivers who have travelled more than 100 KM, in ascending order of NOP:
    SELECT * FROM TRIP WHERE KM > 100 ORDER BY NOP ASC;
  5. To count the number of records per transport code:
    SELECT COUNT(*), TCODE FROM TRIP GROUP BY TCODE HAVING COUNT(*) > 1;
  6. To display distinct transport codes from the TRIP table:
    SELECT DISTINCT TCODE FROM TRIP;
  7. To display the transport code, driver name, and transport type for drivers travelling less than 90 KM:
    SELECT A.TCODE, NAME, TTYPE FROM TRIP A, TRANSPORT B WHERE A.TCODE = B.TCODE AND KM < 90;
  8. To display the driver name and total fare for transporters with TCODE as 105:
    SELECT NAME, KM * PERKM FROM TRIP A, TRANSPORT B WHERE A.TCODE = B.TCODE AND A.TCODE = \'105\';

Question 4:

Write SQL query to add a column total price with datatype numeric and size 10, 2 in a table product.

ALTER TABLE PRODUCT ADD TOTAL_PRICE NUMBER(10, 2);

Question 5:

Sonal needs to display the name of teachers, who have “0” as the third character in their name. She wrote the following query. The query isn\’t producing the result. Identify the problem.

The query written is: SELECT NAME FROM TEACHER WHERE NAME = “$$0?”;

Answer: The wildcards are incorrect. The corrected query is:

SELECT NAME FROM TEACHER WHERE NAME LIKE \'_ _0%\';

Question 6:

Deepika wants to remove all rows from the table BANK but needs to maintain the structure of the table. Which command is used to implement the same?

DELETE FROM BANK;

Question 7:

While creating the table ‘customer’, Rahul forgot to add the column ‘price’. Which command is used to add a new column in the table? Write the command to implement the same.

ALTER TABLE CUSTOMER ADD PRICE NUMBER(10, 2);

Question 8:

What is the use of a wildcard?

Answer:
Wildcard operators are used with the LIKE operator to search for values that match a specific pattern within a column. There are two common wildcard operators:

  • % – Represents any number of characters (zero or more).

  • _ – Represents a single character.

These wildcards allow for flexible and partial matching in SQL queries.

Question 9:

Differentiate between the DELETE and DROP table commands.

Answer:

  • DELETE: The DELETE command is used to remove rows from a table based on a specific condition. It does not affect the table structure. If no condition is specified, it deletes all rows but leaves the table and its structure intact. It is a Data Manipulation Language (DML) command.

  • DROP: The DROP command is used to remove the entire table, including all its rows and structure, from the database. It completely deletes the table from the database. It is a Data Definition Language (DDL) command.

Long Answer Type Questions

Question 1:

Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on the tables COMPANY and CUSTOMER.

SQL Commands:

(i) To display those company names which are having a prize less than 30000:

SELECT NAME FROM COMPANY WHERE PRICE < 30000;

(ii) To display the names of the companies in reverse alphabetical order:

SELECT NAME FROM COMPANY ORDER BY NAME DESC;

(iii) To increase the prize by 1000 for those customers whose name starts with \’S\’:

UPDATE CUSTOMER
SET PRICE = PRICE + 1000
WHERE NAME LIKE \'S%\';

(iv) To add one more column TOTALPRICE with decimal (10,2) to the table CUSTOMER:

ALTER TABLE CUSTOMER
ADD TOTALPRICE DECIMAL(10,2);

(v) To display count of companies in each city:

SELECT COUNT(*), CITY
FROM COMPANY
GROUP BY CITY;

(vi) To display the minimum and maximum prices for customers who bought more than 10 items:

SELECT MIN(PRICE), MAX(PRICE)
FROM CUSTOMER
WHERE QTY > 10;

(vii) To display the average quantity bought by customers whose name contains the letter \’r\’:

SELECT AVG(QTY)
FROM CUSTOMER
WHERE NAME LIKE \'%r%\';

(viii) To display product name, city, and price of the \’MOBILE\’ product from the COMPANY and CUSTOMER tables:

SELECT PRODUCTNAME, CITY, PRICE
FROM COMPANY, CUSTOMER
WHERE COMPANY.CID = CUSTOMER.CID AND PRODUCTNAME = \'MOBILE\';

Question 2:

Consider the following tables SCHOOL and ADMIN and answer the following questions:

SQL Commands:

(i) To display TEACHERNAME and PERIODS of all teachers whose periods are more than 25:

SELECT TEACHERNAME, PERIODS
FROM SCHOOL
WHERE PERIODS > 25;

(ii) To display all the information from the table SCHOOL in descending order of experience:

SELECT * FROM SCHOOL
ORDER BY EXPERIENCE DESC;

(iii) To display DESIGNATION without duplicate entries from the table ADMIN:

SELECT DISTINCT DESIGNATION
FROM ADMIN;

(iv) To display TEACHERNAME, CODE, and corresponding DESIGNATION from tables SCHOOL and ADMIN of male teachers:

SELECT TEACHERNAME, CODE, DESIGNATION
FROM SCHOOL, ADMIN
WHERE SCHOOL.CODE = ADMIN.CODE AND GENDER = \'MALE\';

Question 3:

Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on the tables WATCHES and SALE.

SQL Commands:

(i) To display all the details of those watches whose name ends with \’TIME\’:

SELECT * FROM WATCHES
WHERE WATCH_NAME LIKE \'%TIME\';

(ii) To display watch names and prices of those watches which have a price range between 5000 and 15000:

SELECT WATCH_NAME, PRICE
FROM WATCHES
WHERE PRICE BETWEEN 5000 AND 15000;

(iii) To display total quantity in store of unisex type watches:

SELECT SUM(QTY_STORE)
FROM WATCHES
WHERE TYPE LIKE \'UNISEX\';

(iv) To display watch name and their quantity sold in the first quarter:

SELECT WATCH_NAME, QTY_SOLD
FROM WATCHES W, SALE S
WHERE W.WATCH_ID = S.WATCH_ID
AND QUARTER = 1;

(v) To display the maximum price and minimum quantity stored from the WATCHES table:

SELECT MAX(PRICE), MIN(QTY_STORE)
FROM WATCHES;

(vi) To display total quantity sold in each quarter:

SELECT QUARTER, SUM(QTY_SOLD)
FROM SALE
GROUP BY QUARTER;

(vii) To display watch name, price, and type of watches that were not sold:

SELECT WATCH_NAME, PRICE, TYPE
FROM WATCHES W, SALE S
WHERE W.WATCH_ID != S.WATCH_ID;

(viii) To display watch name, quantity in store, total quantity sold, and stock:

SELECT WATCH_NAME, QTY_STORE, SUM(QTY_SOLD), (QTY_STORE - SUM(QTY_SOLD)) AS STOCK
FROM WATCHES W, SALE S
WHERE W.WATCH_ID = S.WATCH_ID
GROUP BY S.WATCH_ID;

Question 4:

Answer the questions (a) and (b) on the basis of the following tables SHOP and ACCESSORIES.

(a) Write the SQL queries:

(i) To display NAME and PRICE of all accessories in ascending order of their price:

SELECT NAME, PRICE
FROM ACCESSORIES
ORDER BY PRICE ASC;

(ii) To display ID and SNAME of all shops located in Nehru Place:

SELECT ID, SNAME
FROM SHOP
WHERE AREA = \'Nehru Place\';

(iii) To display the minimum and maximum price of each accessory:

SELECT NAME, MIN(PRICE), MAX(PRICE)
FROM ACCESSORIES
GROUP BY NAME;

(iv) To display NAME, PRICE of all accessories and their respective SNAME where they are available:

SELECT NAME, PRICE, SNAME
FROM ACCESSORIES, SHOP
WHERE ACCESSORIES.ID = SHOP.ID;

(b) Write the output of the following SQL:

(i) To display distinct accessory names with a price greater than or equal to 5000:

SELECT DISTINCT NAME
FROM ACCESSORIES
WHERE PRICE >= 5000;

(ii) To display area and count of shops grouped by area:

SELECT AREA, COUNT(*)
FROM SHOP
GROUP BY AREA;

(iii) To display the count of distinct areas from the SHOP table:

SELECT COUNT(DISTINCT AREA)
FROM SHOP;

(iv) To display accessory names and their price after applying a 5% discount for specific shop numbers:

SELECT NAME, PRICE * 0.05 AS DISCOUNT
FROM ACCESSORIES
WHERE SNO IN (\'S02\', \'S03\');

Question 5:

Write SQL queries for the following based on the table APPLICANTS and COURSES:

  1. To display name, fee, gender, joinyear of applicants who have joined before 2010.
    SELECT NAME, FEE, GENDER, JOINYEAR FROM APPLICANTS WHERE JOINYEAR < 2010;
  2. To display names of applicants who are paying fee more than 30000.
    SELECT NAME FROM APPLICANTS WHERE FEE > 30000;
  3. To display names of all applicants in ascending order of their joinyear.
    SELECT NAME FROM APPLICANTS ORDER BY JOINYEAR ASC;
  4. To display the year and total number of applicants joined in each year.
    SELECT JOINYEAR, COUNT(*) FROM APPLICANTS GROUP BY JOINYEAR;
  5. To display C_ID and number of applicants registered in each course.
    SELECT C_ID, COUNT(*) FROM APPLICANTS GROUP BY C_ID;
  6. To display applicant’s name with their respective course’s name.
    SELECT NAME, COURSE FROM APPLICANTS, COURSES WHERE APPLICANTS.C_ID = COURSES.C_ID;

Give the output of the following SQL statements:

  1. SELECT Name, Joinyear FROM APPLICANTS WHERE GENDER=\'F\' AND C_ID=\'A02\';
    Output:
    Avisha    2009
  2. SELECT MIN(Joinyear) FROM APPLICANTS WHERE Gender=\'m\';
    Output:
    2009
  3. SELECT AVG(Fee) FROM APPLICANTS WHERE C_ID=\'A01\' OR C_ID=\'A05\';
    Output:
    67000
  4. SELECT SUM(Fee), C_ID FROM APPLICANTS GROUP BY C_ID HAVING COUNT(*)=2;
    Output:
    55000    A01

Question 6:

Write SQL queries and output based on the tables ITEMS and TRADERS:

  1. To display all items in ascending order of item name:
    SELECT * FROM ITEMS ORDER BY INAME ASC;
  2. To display item name and price for items priced between 10000 and 22000:
    SELECT INAME, PRICE FROM ITEMS WHERE PRICE BETWEEN 10000 AND 22000;
  3. To display number of items traded by each trader:
    SELECT TCODE, COUNT(CODE) FROM ITEMS GROUP BY TCODE;
  4. To display price, item name, and quantity where quantity > 150:
    SELECT PRICE, INAME, QTY FROM ITEMS WHERE QTY > 150;
  5. To display traders from Delhi or Mumbai:
    SELECT TNAME FROM TRADERS WHERE CITY = \'DELHI\' OR CITY = \'MUMBAI\';
  6. To display company name and item name in descending order of company:
    SELECT COMPANY, INAME FROM ITEMS ORDER BY COMPANY DESC;

Outputs for the following queries:

  1. SELECT MAX(PRICE), MIN(PRICE) FROM ITEMS;
    Output: 38000, 1200
  2. SELECT PRICE*QTY FROM ITEMS WHERE CODE=1004;
    Output: 1075000
  3. SELECT DISTINCT TCODE FROM ITEMS;
    Output: T01, T02, T03
  4. SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE I.TCODE=T.TCODE AND QTY < 100;
    Output:
    LED SCREEN 40    DISP HOUSE INC
    CAR GPS SYSTEM    ELECTRONICS SALES

Question 7:

Write SQL queries and output based on the tables PRODUCTS and SUPPLIERS:

  1. To display all products in ascending order of product name:
    SELECT * FROM PRODUCTS ORDER BY PNAME ASC;
  2. To display product name and price for products priced between 10000 and 15000:
    SELECT PNAME, PRICE FROM PRODUCTS WHERE PRICE BETWEEN 10000 AND 15000;
  3. To display number of products supplied by each supplier:
    SELECT SUPCODE, COUNT(PID) FROM PRODUCTS GROUP BY SUPCODE;
  4. To display price, product name, and quantity where quantity > 100:
    SELECT PRICE, PNAME, QTY FROM PRODUCTS WHERE QTY > 100;
  5. To display suppliers from Delhi or Chennai:
    SELECT SNAME FROM SUPPLIERS WHERE CITY = \'DELHI\' OR CITY = \'CHENNAI\';
  6. To display company and product names in descending order of company name:
    SELECT COMPANY, PNAME FROM PRODUCTS ORDER BY COMPANY DESC;

Outputs for the following queries:

  1. SELECT DISTINCT SUPCODE FROM PRODUCTS;
    Output: S01, S02, S03
  2. SELECT MAX(PRICE), MIN(PRICE) FROM PRODUCTS;
    Output: 28000, 1100
  3. SELECT PRICE*QTY FROM PRODUCTS WHERE PID = 104;
    Output: 550000
  4. SELECT PNAME, SNAME FROM PRODUCTS P, SUPPLIERS S WHERE P.SUPCODE = S.SUPCODE AND QTY > 100;
    Output:
    DIGITAL CAMERA 14X    GETALL INC
    PENDRIVE 16GB    GETALL INC

Question 8:

Consider the tables CARDEN and CUSTOMER and answer the following:

(a) Example to illustrate Primary and Alternate Keys:

  • Primary Key of CARDEN = Ccode
  • Alternate Key = CarName
  • Primary Key of CUSTOMER = Code
  • Alternate Key = Cname

(b) SQL Queries:

  1. SELECT CarName FROM CARDEN WHERE Color = \'SILVER\';
  2. SELECT CarName, Make, Capacity FROM CARDEN ORDER BY Capacity DESC;
  3. SELECT MAX(Charges) FROM CARDEN;
  4. SELECT Cname, CarName FROM CARDEN, CUSTOMER WHERE CARDEN.Ccode = CUSTOMER.Ccode;

(c) Outputs:

  1. SELECT COUNT(DISTINCT Make) FROM CARDEN;
    Output: 4
  2. SELECT MAX(Charges), MIN(Charges) FROM CARDEN;
    Output: 35, 112
  3. SELECT COUNT(Ccode), Make FROM CARDEN;
    Output: 5
  4. SELECT Cname FROM CUSTOMER WHERE Ccode = CARDEN.Ccode AND CarName = \'SX4\';
    Output: SX4, C Class

Question 9:

Consider the following tables CABHUB and CUSTOMER and answer the following:

(a) Example to Illustrate Primary and Candidate Keys:

VcodeVehicleNameMakeCapacityColourCharges
V101SX4Maruti4White18
V102InnovaToyota7Black25
V103CityHonda4Silver22
V104C ClassMercedes4White35

Primary Key: Vcode
Candidate Key: VehicleName

(b) SQL Commands:

  1. To display the names of all the white coloured vehicles:
    SELECT VehicleName FROM CABHUB WHERE Colour = \'WHITE\';
  2. To display vehicle name and capacity in ascending order of sitting capacity:
    SELECT VehicleName, Capacity FROM CABHUB ORDER BY Capacity ASC;
  3. To display the highest charges at which a vehicle can be hired:
    SELECT MAX(Charges) FROM CABHUB;
  4. To display customer name and vehicle name hired by them:
    SELECT Cname, VehicleName FROM CABHUB, CUSTOMER WHERE CUSTOMER.Vcode = CABHUB.Vcode;

(c) Output of SQL Queries:

  1. SELECT COUNT(DISTINCT Make) FROM CABHUB;
    Output: 4
  2. SELECT MAX(Charges), MIN(Charges) FROM CABHUB;
    Output: MAX(Charges): 35, MIN(Charges): 12
  3. SELECT COUNT(*) Make FROM CABHUB;
    Output: 5
  4. SELECT Vehicle FROM CABHUB WHERE Capacity=4;
    Output: SX4, C Class

Question 10:

Consider the following tables EMPLOYEE and DEPARTMENT and answer the following:

(a) SQL Commands:

  1. Display all DepName along with DepCde in descending orde
    r of DepCde:
    SELECT DepName, DEPARTMENT.DepCde FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.DepCde = DEPARTMENT.DepCde ORDER BY DEPARTMENT.DepCde DESC;
  2. Display average age of employees in DepCde = 103:
    SELECT AVG(Age) FROM EMPLOYEE WHERE DepCde = \'103\';
  3. Display the name of DepHead of the Employee named \”Sanjeev P\”:
    SELECT DeptHead FROM DEPARTMENT WHERE DEPARTMENT.DepCde = (SELECT DepCde FROM EMPLOYEE WHERE TName = \'Sanjeev P\');
  4. Display details of all employees who joined before 2007:
    SELECT * FROM EMPLOYEE WHERE JoinDate < \'01-JAN-2007\';

(b) Output of SQL Queries:

  1. SELECT COUNT(DISTINCT DepCde) FROM EMPLOYEE;
    Output: 3
  2. SELECT MAX(JoinDate), MIN(JoinDate) FROM EMPLOYEE;
    Output: MAX: 22-OCT-2010, MIN: 01-MAY-2003
  3. SELECT TName, DepHead FROM EMPLOYEE E, DEPARTMENT D WHERE E.DepCde = D.DepCde;
    Output: Returns a list of Employee Names and their respective Department Heads
  4. SELECT COUNT(*) FROM EMPLOYEE WHERE Salary > 60000 AND Age > 30;
    Output: 2

Question 11:

Consider the following tables WORKER and PAYLEVEL and answer the following:

(a) SQL Commands:

  1. Display the names of all Workers in descending order of DOB:
    SELECT NAME FROM WORKER ORDER BY DOB DESC;
  2. Display NAME and DESIGN of workers whose PLEVEL is either \’P001\’ or \’P002\’:
    SELECT NAME, DESIGN FROM WORKER WHERE PLEVEL = \'P001\' OR PLEVEL = \'P002\';
  3. Display content of all workers whose DOB is between \’19-JAN-1984\’ and \’18-JAN-1987\’:
    SELECT * FROM WORKER WHERE DOB BETWEEN \'19-JAN-1984\' AND \'18-JAN-1987\';
  4. Insert a new row:
    INSERT INTO WORKER VALUES (19, \'DayaKishore\', \'Operator\', \'P003\', \'19-Sep-2008\', \'11-Jul-1984\');

(b) Output of SQL Queries:

  1. SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;
    Output: Group-wise count of workers per PLEVEL
  2. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;
    Output: MAX(DOB): 23-DEC-1987, MIN(DOJ): 01-JAN-2002
  3. SELECT Name, PAY FROM WORKER W, PAYLEVEL P WHERE W.PLEVEL = P.PLEVEL AND W.ECODE < 13;
    Output: Names and pay details of workers with ECODE less than 13
  4. SELECT * FROM PAYLEVEL WHERE PLEVEL = \'P003\';
    Output: Full row from PAYLEVEL table where PLEVEL = \’P003\’

Question 12:

Consider the following tables EMPLOYEE and SALGRADE and answer (b) and (c) parts of this question:

(a) What do you understand by Selection and Projection operations in relational algebra?

Projection (π): In relational algebra, projection is a unary operation that selects certain columns (attributes) from a relation and discards the rest. For example, πname, salary(EMPLOYEE) will return only the name and salary columns from the EMPLOYEE table.

Selection (σ): In relational algebra, selection is a unary operation used to select rows (tuples) that satisfy a given predicate. For example, σsalary > 5000(EMPLOYEE) selects all employees with a salary greater than 5000.

(b) Write SQL commands for the following statements:

  1. To display the details of all EMPLOYEES in descending order of DOJ:
    SELECT * FROM EMPLOYEE ORDER BY DOJ DESC;
  2. To display NAME and DESIGN of those EMPLOYEES, whose SALGRADE is either S02 or S03:
    SELECT NAME, DESIGN FROM EMPLOYEE WHERE SGRADE = \'S02\' OR SGRADE = \'S03\';
  3. To display the content of all the EMPLOYEES table, whose DOJ is between ’09-Feb-2006′ and ’08-Aug-2009′:
    SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN \'09-FEB-2006\' AND \'08-AUG-2009\';
  4. To add a new row:
    INSERT INTO EMPLOYEE VALUES (109, \'Harish Roy\', \'HEAD-IT\', \'S02\', \'09-SEP-2007\', \'21-APR-1983\');

(c) Give the output of the following SQL queries:

SELECT COUNT(SGRADE), SGRADE FROM EMPLOYEE GROUP BY SGRADE;
SELECT MIN(DOB), MAX(DOJ) FROM EMPLOYEE;
SELECT NAME, SALARY FROM EMPLOYEE E, SALGRADE S WHERE E.SGRADE = S.SGRADE AND E.ECODE < 103;
SELECT SGRADE, SALARY + HRA FROM SALGRADE WHERE SGRADE = \'S02\';

Question 13:

Consider the following tables GAMES and PLAYER and answer (b) and (c) parts of this question:

(a) What do you understand by primary key and candidate keys?

Primary Key: An attribute or set of attributes that uniquely identifies each record in a table.

Candidate Keys: Attributes that can uniquely identify tuples. One of them is chosen as the primary key.

(b) Write SQL commands for the following statements:

  1. To display the name of all GAMES with their GCodes:
    SELECT GameName, GCode FROM GAMES;
  2. To display details of those GAMES which are having PrizeMoney more than 7000:
    SELECT * FROM GAMES WHERE PrizeMoney > 7000;
  3. To display the content of the GAMES table in ascending order of Schedule Date:
    SELECT * FROM GAMES ORDER BY ScheduleDate;
  4. To display sum of PrizeMoney for each type of GAMES:
    SELECT Type, SUM(PrizeMoney) FROM GAMES GROUP BY Type;

(c) Give the output of the following SQL queries:

SELECT COUNT(DISTINCT Number) FROM GAMES;
-- Output: 2
SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM GAMES;
-- Output: 19-Mar-2004 12-Dec-2003
SELECT Name, GameName FROM GAMES G, PLAYER P WHERE G.GCode = P.GCode AND G.PrizeMoney > 10000;
-- Output: Ravi Sahai  Lawn Tennis
SELECT DISTINCT GCode FROM PLAYER;
-- Output: 101 108 103

Question 14:

Consider the following tables ACTIVITY and COACH and answer (a) and (b) parts of this question:

(a) Write SQL commands for the following statements:

  1. To display the names of all activities with their Acodes in descending order:
    SELECT ACode, ActivityName FROM ACTIVITY ORDER BY ACode DESC;
  2. To display sum of PrizeMoney for the Activities played in each of the Stadium separately:
    SELECT Stadium, SUM(PrizeMoney) FROM ACTIVITY GROUP BY Stadium;
  3. To display the coach’s name and Acodes in ascending order of Acode:
    SELECT Name, ACode FROM COACH ORDER BY ACode ASC;
  4. To display the content of the Activity table whose schedule date is earlier than 01-01-2004 in ascending order of ParticipantsNum:
    SELECT * FROM ACTIVITY WHERE ScheduleDate < \'01-JAN-2004\' ORDER BY ParticipantsNum ASC;

(b) Give the output of the following SQL queries:

SELECT COUNT(DISTINCT ParticipantsNum) FROM ACTIVITY;
-- Output: 3
SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM ACTIVITY;
-- Output: 19-Mar-2004 12-Dec-2003
SELECT Name, ActivityName FROM ACTIVITY A, COACH C WHERE A.ACode = C.ACode AND A.ParticipantsNum = 10;
-- Output: Ravinder Discuss Throw
SELECT DISTINCT ACode FROM COACH;
-- Output: 1001 1008 1003

Question 15:

Consider the following tables RESORT and OWNEDBY and answer (a) and (b) parts of this question:

(a) Write SQL commands for the following statements:

  • SELECT RCODE, PLACE FROM RESORT WHERE TYPE = \’5 STAR\’ ORDER BY PLACE;
  • SELECT MAX(RENT), MIN(RENT) FROM RESORT GROUP BY TYPE;
  • SELECT * FROM RESORT WHERE STARTDATE > \’31-DEC-05\’;
  • SELECT OWNER FROM RESORT A, OWNEDBY B WHERE A.TYPE = \’5 STAR\’ AND A.PLACE = B.PLACE;

(b) Give output for the following SQL queries:

  • SELECT MIN(RENT) FROM RESORT WHERE PLACE = \’KERALA\’;
  • SELECT TYPE, STARTDATE FROM RESORT WHERE TYPE = \’2 STAR\’ ORDER BY STARTDATE;
  • SELECT PLACE, OWNER FROM OWNEDBY WHERE PLACE LIKE \’%A\’;
  • SELECT RCODE, RENT FROM RESORT, OWNEDBY WHERE RESORT.PLACE = OWNEDBY.PLACE AND TYPE = \’3 STAR\’;

Question 16:

Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question:

(a) Write SQL commands for the following statements:

  • SELECT * FROM STORE ORDER BY LastBuy ASC;
  • SELECT ItemNo, Item FROM STORE WHERE Rate > 15;
  • SELECT * FROM STORE WHERE Scode = \’22\’ OR Qty > 110;
  • SELECT Sname, MIN(Rate) FROM STORE, SUPPLIERS WHERE STORE.Scode = SUPPLIERS.Scode GROUP BY Sname;

(b) Give the output of the following SQL queries:

  • SELECT COUNT(DISTINCT Scode) FROM STORE;
    Output: 3
  • SELECT Rate * Qty FROM STORE WHERE ItemNo = 2004;
    Output: 880
  • SELECT Item, Sname FROM STORE S, SUPPLIERS P WHERE S.Scode = P.Scode AND ItemNo = 2006;
    Output: Gel Pen Classic, Premium Stationers
  • SELECT MAX(LastBuy) FROM STORE;
    Output: 24-Feb-10

Question 17:

Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question:

(a) Write SQL commands for the following statements:

  • SELECT * FROM STOCK ORDER BY StockDate;
  • SELECT ItemNo, Item FROM STOCK WHERE UnitPrice > 10;
  • SELECT * FROM STOCK WHERE Dcode = \’102\’ OR Qty > 100;
  • SELECT Dcode, MAX(UnitPrice) FROM STOCK GROUP BY Dcode;

(b) Give the output of the following SQL queries:

  • SELECT COUNT(DISTINCT Dcode) FROM STOCK;
    Output: 3
  • SELECT Qty * UnitPrice FROM STOCK WHERE ItemNo = 5006;
    Output: 4400
  • SELECT Item, Dname FROM STOCK S, DEALERS D WHERE S.Dcode = D.Dcode AND ItemNo = 5004;
    Output: Eraser Big, Clear Deals
  • SELECT MIN(StockDate) FROM STOCK;
    Output: 01-Jan-09