Ch 29: SQL Aggregate Functions & GROUP BY

Technical study of aggregated reporting, comparing WHERE clause filtering (row-level) with HAVING clause filtering (group-level), and NULL handling.

9 Questions9 MCQs0 Short Questions0 Long Questions

🎯 Exam Practice Papers

CS403 - Database Management Systems Examination Practice

Timed practice exam simulation for CS403 - Database Management Systems.

Showing 1 – 9 of 9 Questions

Page 1 of 1

❓ MCQ📘 CS403📑 Lec 29🏷️ 29.5. Pattern Matching with LIKE operator (% and _)Easy🔁⭐💡📄🎓VU🏛️VU

In Structured Query Language (SQL), wildcard characters (such as '%' and '_') are standardly used with which logical comparison operator to perform pattern matching on string columns?

💻 sql Snippet

SELECT * FROM student 
WHERE stName LIKE 'Dr.%';

A.NOT NULL
B.LIKE
C.BINARY
D.NULL

❓ MCQ📘 CS403📑 Lec 29🏷️ 29.5. Pattern Matching with LIKE operator (% and _)Easy🔁⭐💡📄🎓VU🏛️VU

When constructing database pattern matching queries using the LIKE operator, which wildcard character allows matching any sequence of characters of any length, including a length of zero?

💻 sql Snippet

SELECT * FROM Teacher 
WHERE Name LIKE 'Dr.%';

A.The hash symbol (#)
B.The percent symbol (%)
C.The underscore symbol (_)
D.The hyphen symbol (-)

❓ MCQ📘 CS403📑 Lec 29🏷️ 29.2. Where Clause and Operaors(Not, BETWEEN,IN, Like)Medium🔁⭐💡🌟📄🎓VU🏛️VU

For a logistics tracking system with a 'Packages' table, write an SQL query to retrieve distinct records of delivered packages where the transport vehicle is NOT a 'Van', the shipping date falls within the inclusive range of '2024-03-01' and '2024-03-29', and the delivery status is strictly 'Delivered'. The final output must be sorted by the delivery date in descending chronological order.

💻 sql Snippet

SELECT DISTINCT SenderCity, PackageID, RecipientCity, ShippingDate, DeliveryDate, Weight, Status, VehicleType
FROM Packages
WHERE VehicleType != 'Van'
AND ShippingDate BETWEEN '2024-03-01' AND '2024-03-29'
AND Status = 'Delivered'
ORDER BY DeliveryDate DESC;

A.SELECT DISTINCT SenderCity, PackageID, RecipientCity, ShippingDate, DeliveryDate, Weight, Status, VehicleType FROM Packages WHERE VehicleType!= 'Van' AND ShippingDate BETWEEN '2024-03-01' AND '2024-03-29' AND Status = 'Delivered' ORDER BY DeliveryDate DESC;
B.SELECT DISTINCT SenderCity, PackageID, RecipientCity, ShippingDate, DeliveryDate, Weight, Status, VehicleType FROM Packages WHERE VehicleType = 'Van' OR ShippingDate BETWEEN '2024-03-01' AND '2024-03-29' OR Status = 'Delivered' ORDER BY DeliveryDate DESC;
C.SELECT DISTINCT SenderCity, PackageID, RecipientCity, ShippingDate, DeliveryDate, Weight, Status, VehicleType FROM Packages WHERE VehicleType <> 'Van' AND ShippingDate NOT BETWEEN '2024-03-01' AND '2024-03-29' AND Status = 'Delivered' ORDER BY DeliveryDate ASC;
D.SELECT SenderCity, PackageID, RecipientCity, ShippingDate, DeliveryDate, Weight, Status, VehicleType FROM Packages WHERE VehicleType NOT IN ('Van') AND ShippingDate IN ('2024-03-01', '2024-03-29') AND Status = 'Delivered' ORDER BY DeliveryDate DESC;

❓ MCQ📘 CS403📑 Lec 29🏷️ 29.4. Special Operators (BETWEEN, IN, LIKE)Easy🔁⭐💡📄🎓VU🏛️VU

True or False: The SQL 'IN' function helps reduce the need to use multiple 'AND' conditions in a query predicate.

💻 sql Snippet

SELECT * FROM EMP WHERE DeptName IN ('Marketing', 'Accounting');

A.True
B.False

❓ MCQ📘 CS403📑 Lec 29🏷️ 29.4. Special Operators (BETWEEN, IN, LIKE)Easy🔁⭐💡📄🎓VU🏛️VU

True or False: The SQL 'IN' function helps reduce the need to use multiple 'AND' conditions.

💻 sql Snippet

-- Shorthand IN operator matching multiple OR conditions:
SELECT * FROM STUDENT WHERE prName IN ('BCS', 'MCS', 'BIT');

A.True
B.False

❓ MCQ📘 CS403📑 Lec 29🏷️ 29.1. SQL WHERE ClauseEasy🔁⭐💡📄🎓VU🏛️VU

Consider a customer database table named 'CUST' containing the attributes: 'customer_id', 'first_name', 'last_name', and 'phone'. Which of the following SQL statements correctly retrieves all information of the customers whose last name is 'ALI'?

💻 sql Snippet

SELECT * FROM CUST WHERE last_name = 'ALI';

A.SELECT * FROM CUST WHERE last_name = 'ALI';
B.SELECT * FROM CUST WHERE last_name = ALI;
C.SELECT * FROM CUSTOMER WHERE name = ALI;
D.SELECT * FROM CUSTOMER WHERE last_name = ALI;

❓ MCQ📘 CS403📑 Lec 29🏷️ 29.3. Logical Operators (AND, OR, NOT)Hard⭐💡🌟📄🎓VU🏛️VU

Consider an 'Executives' database table containing the following records: - ExecID: 101, Name: 'Zafar', Department: 'IT', Salary: 85000 - ExecID: 102, Name: 'Ali', Department: 'Marketing', Salary: 72000 - ExecID: 103, Name: 'Nasir', Department: 'Finance', Salary: 95000 - ExecID: 104, Name: 'Latif', Department: 'IT', Salary: 64000 What will be the exact result set output of executing the following SQL query? SELECT Name, Salary FROM Executives WHERE Department = 'IT' AND Salary > 70000 ORDER BY Salary DESC;

💻 sql Snippet

SELECT Name, Salary 
FROM Executives 
WHERE Department = 'IT' AND Salary > 70000 
ORDER BY Salary DESC;

A.Name: 'Zafar', Salary: 85000
B.Name: 'Nasir', Salary: 95000 Name: 'Zafar', Salary: 85000
C.Name: 'Zafar', Salary: 85000 Name: 'Latif', Salary: 64000
D.Name: 'Zafar', Salary: 85000 Name: 'Ali', Salary: 72000

📝 Short Q📘 CS403📑 Lec 29🏷️ 29.5. Pattern Matching with LIKE operator (% and _)Medium🔁⭐💡📄🎓VU🏛️VU

Analyze the following SQL query targeting a table named 'Persons': ```sql SELECT * FROM Persons WHERE Firstname LIKE '% da %'; ``` Explain what this statement returns and describe the pattern-matching behavior of the wildcard operators.

💻 sql Snippet

SELECT * FROM Persons WHERE Firstname LIKE '% da %';

📝 Short Q📘 CS403📑 Lec 29🏷️ 29.1. SQL WHERE ClauseMedium🔁⭐💡📄🎓VU🏛️VU

Given two tables, 'Active_Students' and 'Honors_List', both containing union-compatible schemas with the columns: 'stId', 'stName', and 'cgpa'. Write an SQL query using the INTERSECT operator to retrieve only those students who appear in both tables.

💻 sql Snippet

SELECT stId, stName, cgpa FROM Active_Students
INTERSECT
SELECT stId, stName, cgpa FROM Honors_List;

Ch 29: SQL Aggregate Functions & GROUP BY

CS403 - Database Management Systems Examination Practice

Mid Term Examination

Final Term Examination

CS403 - Database Management Systems Examination Practice

Mid Term Examination

Final Term Examination

📖 Related CS403 - Database Management Systems Curriculum Units