SQL Test 2

    Question 1: Selecting All Columns

    Which query retrieves all columns from the students table?

    Question 2: Numeric Data Type Operations

    In a products table with columns (product_id INT, price DECIMAL(10,2), discount_percent INT), write a query to calculate the final price after applying discount for products with price greater than $100.

    Question 3: INSERT vs INSERT INTO

    What is the difference between INSERT and INSERT INTO?

    Question 4: GROUP BY with HAVING Clause

    From an employees table (emp_id, name, department, salary), find departments with more than 5 employees and average salary greater than $60,000.

    Question 5: Basic SELECT with Multiple WHERE Conditions

    From an employees table with columns (emp_id, name, department, salary, hire_date), select name and salary for employees in 'Sales' or 'Marketing' departments earning between $40,000 and $80,000.

    Question 6: Clustered vs Non-Clustered Indexes

    What is the difference between clustered and non-clustered indexes?

    Question 7: BETWEEN Inclusive vs Exclusive

    Is the BETWEEN operator inclusive or exclusive of boundary values?

    Question 8: INNER JOIN vs WHERE with Multiple Tables

    What is the difference between INNER JOIN and using WHERE clause with multiple tables?

    Question 9: LEFT JOIN vs INNER JOIN

    What is the difference between LEFT JOIN and INNER JOIN?

    Question 10: Counting Rows

    Which query counts the total number of students in the students table?

    Question 11: Filtering Rows with WHERE

    Write a query to find all employees whose department is 'HR'.

    Question 12: TEXT vs VARCHAR Length Limits

    What is the difference between TEXT and VARCHAR data types?

    Question 13: Table Modification with ALTER

    You have an existing products table and need to: add a new column description (TEXT), modify price column to allow larger values DECIMAL(12,2), and drop the old_code column.

    Question 14: SELECT Columns with GROUP BY Rule

    What is the rule for SELECT columns when using GROUP BY?

    Question 15: NULL Handling in WHERE Clause

    How should NULL values be handled in WHERE clause?

    Question 16: Creating a Composite Index

    Your queries often filter results based on a combination of country and city from the users table. Which query correctly creates a single index to optimize these types of searches?

    Question 17: Complex AND, OR, NOT Operators

    From an employees table (emp_id, name, department, salary, city), find employees who work in 'IT' or 'Finance' departments, earn more than $50,000, but are NOT located in 'New York'.

    Question 18: INNER JOIN with Multiple Conditions

    Can INNER JOIN have multiple conditions in the ON clause?

    Question 19: RIGHT OUTER JOIN vs. LEFT OUTER JOIN

    You want to produce a list of all departments from a departments table (dept_id, dept_name), including those that currently have no employees assigned, by joining with an employees table (emp_id, name, dept_id). Which join should you use?

    Question 20: SUM and AVG Functions with Grouping

    From a sales table (sale_id, salesperson_id, product_id, quantity, unit_price), calculate total sales amount and average sale amount per salesperson.