SQL Test 4

    Question 1: Foreign Key Concept

    What is a foreign key?

    Question 2: Date Function Operations

    Given an orders table with columns (order_id INT, order_date DATE, delivery_date DATE), write a query to find orders where delivery took more than 7 days.

    Question 3: DDL vs DML Transaction Behavior

    How do DDL and DML commands behave differently in transactions?

    Question 4: GROUP BY with Complex HAVING Conditions

    From a student_grades table (student_id, subject, grade, semester), find students who have taken more than 3 subjects and have an average grade between 85 and 95, showing student_id, subject count, and average grade.

    Question 5: SELECT with Date Range and Ordering

    From an orders table (order_id, customer_id, order_date, total_amount, status), select all columns for orders placed in 2023 with status 'Completed', ordered by total_amount in descending order.

    Question 6: Creating a Unique Index

    You need to ensure that the combination of product_id and supplier_id in a product_suppliers table is always unique, while also improving query performance on these columns. Which statement achieves both goals?

    Question 7: Operator Precedence in SQL

    What is the precedence order of logical operators in SQL?

    Question 8: INNER JOIN vs CROSS JOIN

    What is the difference between INNER JOIN and CROSS JOIN?

    Question 9: Multiple OUTER JOINs in Single Query

    Can multiple OUTER JOINs be used in a single query?

    Question 10: Combined Aggregate Functions with Conditions

    From a products table (product_id, product_name, category, price, stock_quantity), show category-wise statistics: total products, average price, minimum price, maximum price, and total stock, but only for categories with more than 2 products.

    Question 11: SQL Constraints Overview

    What are constraints in SQL?

    Question 12: String Function with Data Types

    Given a users table with columns (user_id INT, username VARCHAR(50), email VARCHAR(100)), write a query to find all users whose username length is greater than 8 characters.

    Question 13: CREATE TABLE with Constraints

    Which constraints can be defined during CREATE TABLE?

    Question 14: GROUP BY Multiple Columns with WHERE and HAVING

    From a sales table (sale_id, product_id, category, region, sale_amount, sale_date), find total sales by category and region for sales in 2023, showing only combinations with total sales exceeding $10,000.

    Question 15: Wildcard Characters in LIKE

    What wildcard characters are used with LIKE operator?

    Question 16: Index Scan vs Index Seek

    What is the difference between index scan and index seek?

    Question 17: BETWEEN with Dates

    How does BETWEEN operator work with date values?

    Question 18: Column Selection in INNER JOIN

    How do you select columns from specific tables in INNER JOIN?

    Question 19: OUTER JOIN with WHERE Clause Filtering

    What happens when WHERE clause filters on OUTER JOIN columns?

    Question 20: Finding the Highest Salary

    Which query finds the maximum salary from the employees table?