Page 10 - IPP-12-2025
P. 10

Table: ORDERS

                               Order_ID              Cust_ID                Amount
                               6009                  2101                   1800
                               6010                  2104                   2575
                               6011                  2102                   3291
                               6012                  2103                   1980
                               6013                  2105                   5400
                     Write appropriate SQL queries for the following:
                     (a)  Display the customers’ names and their order amounts.
                     (b)  Display the details of orders whose amount is greater than 3500.
                     (c)  Display the total number of customers based on their addresses.
                                                               Or
                     Consider the following tables:
                     Table 1:

                     STUDENTS,  which  stores  RollNo,  Name,  Class,  Subject.  This  table  displays  basic  information  about
                     students.
                     Table 2:
                     PROJECTS, which stores Project_ID, RollNo and Title. This table displays information about projects.

                                                        Table: STUDENTS

                                   RollNo          Name            Class         Subject
                                     1     Mohita Arora             12   Economics
                                     2     Hriday Kapoor            12   Informatics Practices
                                     3     Shanaya Jain             12   Accountancy
                                     4     Arham Kumar              12   Business Studies
                                     5     Ayaan Goel               12   Informatics Practices
                                                         Table: PROJECTS

                                       Project_ID    RollNo   Title
                                          1201          1     Globalization
                                          1202          2     Bank Management System
                                          1203          3     Partnership Firm
                                          1204          4     Green Business Initiatives
                                          1205          5     Library Management System

                     Write appropriate SQL queries for the following:
                     (a)  Display the details of projects whose title contains letter ‘I’.
                     (b)  Display the details of students subject-wise.
                     (c)  Display the roll number, student name and project title of students whose rollno is greater than 2.
                Ans.  (a)  SELECT Name, Amount FROM CUSTOMERS C, ORDERS O WHERE C. Cust_ID = O.
                         Cust_ID;
                     (b)  SELECT * FROM ORDERS WHERE Amount > 3500;
                     (c)  SELECT Address, Count(*) FROM CUSTOMERS GROUP BY Address;
                                                               Or
                     (a)  SELECT * FROM PROJECTS WHERE Title LIKE '% i %';
                     (b)  SELECT * FROM STUDENTS GROUP BY SUBJECT;
                     (c)  SELECT RollNo, Name, Title FROM STUDENTS S, PROJECTS P WHERE S. RollNo =
                         P. RollNo;
                                                                                    Model Test Paper         M.7
   5   6   7   8   9   10   11   12   13   14   15