MySQL Training in Virudhunagar

1. What are constraints in SQL, and why are they important?

2. What are the different types of constraints available in SQL?

3. What is a PRIMARY KEY constraint, and how does it differ from a UNIQUE constraint?

4. What is a FOREIGN KEY constraint, and why is it used?

5. What is the purpose of the CHECK constraint, and how does it work?

6. What happens if a UNIQUE constraint is violated during an INSERT or UPDATE operation?

7. Can a table have multiple FOREIGN KEY constraints? If yes, provide an example.

8. What is the purpose of the DEFAULT constraint, and how does it work?

9. Can you have a column with both NOT NULL and UNIQUE constraints?

10. What are the differences between a PRIMARY KEY constraint and a FOREIGN KEY constraint?

1. What is Data Definition Language (DDL) in SQL?

2. What is the purpose of the CREATE statement in SQL?

3. What is the difference between DROP and TRUNCATE commands in SQL?

4. What does the ALTER statement do in SQL?

5. Explain the purpose of the RENAME command in SQL.

6. What is the difference between ALTER TABLE and CREATE TABLE commands?

7. What are the different types of database objects you can create using SQL?

8. What is the purpose of specifying a DEFAULT value when creating a table?

9. What is a CHECK constraint, and how is it used in the CREATE TABLE statement?

10. How do you specify a primary key when creating a table?

Query Questions:

1. Write an SQL statement to create a table named employees with the following columns: employee_id (integer, primary key), first_name (varchar, up to 50 characters), last_name (varchar, up to 50 characters), and hire_date (date).

2. Write an SQL statement to add a new column named department (varchar, up to 30 characters) to the existing employees table.

3. Write an SQL statement to modify the hire_date column in the employees table to have a default value of the current date.

4. Write an SQL statement to rename the department column to dept in the employees table.

5. Write an SQL statement to drop the employees table and ensure that any dependent objects are also removed.

6. Write an SQL statement to create a table named departments with the following columns: department_id (integer, primary key), department_name (varchar, up to 100 characters), and location (varchar, up to 50 characters).

7. Write an SQL statement to drop the salary column from the employees table.

8. Write an SQL statement to create a UNIQUE constraint on the email column in the employees table to ensure no duplicate email addresses are allowed.

9. Write an SQL statement to create a table named customers with the following columns: customer_id (integer, primary key), name (text), email (text, unique), and created_at (timestamp, default to the current timestamp).

10. Write an SQL statement to create a table named products with the following specifications:

product_id (integer, primary key, auto-increment)
product_name (varchar, up to 100 characters)
price (decimal, up to 10 digits with 2 decimal places)
in_stock (boolean, default to TRUE)

11. Write an SQL statement to create a table named orders with a foreign key reference to the customer_id column in the customers table. The orders table should also include an order_id (primary key), order_date (date), and amount (decimal).

1. What is Data Manipulation Language (DML) in SQL?

2. What is the purpose of the SELECT statement in SQL?

3. How does the INSERT statement work in SQL, and what are its variations?

4. What is the purpose of the UPDATE statement in SQL?

5. What is the function of the DELETE statement in SQL?

6. What are the differences between DELETE and TRUNCATE commands in SQL?

7. How can you use the WHERE clause in SELECT, UPDATE, and DELETE statements?

8. What is the Purpose of OrderBy Clause in SQL ?

9. What is the Purpose of Where Clause in SQL ?

Query Questions:

1. Write an SQL query to retrieve all columns from a table named employees where the salary is greater than 50,000.

2. Write an SQL query to insert a new row into the employees table with employee_id of 101, first_name as ‘Alice’, last_name as ‘Johnson’, and hire_date as ‘2024-08-12’.

3. Write an SQL query to update the salary of employees in the employees table to 55,000 where the department is ‘Sales’.

4. Write an SQL query to delete all rows from the employees table where hire_date is before January 1, 2023.

5. Write an SQL query to retrieve the average salary of employees in each department from the employees table. The result should include department and average_salary.

6. Write an SQL query to find the highest salary in the employees table and display it with the column name highest_salary.

7. Write an SQL query to retrieve the top 5 highest salaries from the employees table.

8. Write an SQL query to count the number of employees in each department and display the results with columns department and employee_count.

9. Write an SQL query to retrieve all distinct job titles from the employees table.

10. Write an SQL query to select employees whose first_name starts with ‘J’ and sort the results by last_name in ascending order.

1. What is a SQL join?

2. What are the different types of SQL joins?

3. What is the difference between INNER JOIN and OUTER JOIN?

4. How does a CROSS JOIN differ from other types of joins?

5. Can you use multiple joins in a single SQL query?

Query Questions:

1. Write an SQL query to retrieve the order_id, customer_name, and order_date from two tables: orders and customers. Assume orders has a customer_id that relates to customers.customer_id. Use an INNER JOIN.

2. Write an SQL query to find all employees from the employees table who have not been assigned to any department in the departments table. Use a LEFT JOIN.

3. Write an SQL query to get the total number of orders for each customer. The query should return customer_id and total_orders. Use an INNER JOIN between the orders and customers tables.

4. Write an SQL query to retrieve all products from the products table that have not been sold. Assume products has a product_id column and sales has a product_id column. Use a LEFT JOIN.

5. Write an SQL query to find the average salary of employees in each department. Use an INNER JOIN between the employees and departments tables, and group the results by department_name.

6. Write an SQL query to list all employees and their managers. Assume the employees table has a manager_id column that references employee_id of the same table. Use a SELF JOIN.

7. Write an SQL query to list the products along with the number of times each product has been ordered. Use an INNER JOIN between products and order_details tables, assuming order_details has a product_id column.

8. Write an SQL query to retrieve all employees who work in the same department as ‘John Doe’. Use a subquery and an INNER JOIN.

9. Write an SQL query to find all customers who have placed more than 5 orders. Use an INNER JOIN between customers and orders tables, and group the results by customer_id.

10. Write an SQL query to list all authors and the number of books they have written. Assume authors has author_id and books has author_id as a foreign key. Use an INNER JOIN.

1. What are aggregate functions in SQL?

2. What is the purpose of the GROUP BY clause in SQL?

3. How does the HAVING clause differ from the WHERE clause?

4. Can you use GROUP BY without aggregate functions?

5. What is the purpose of the ORDER BY clause in conjunction with GROUP BY?

Query Questions:

1. Write an SQL query to find the total number of orders placed by each customer. Display customer_id and total_orders. Use the COUNT() function and GROUP BY clause.

2. Write an SQL query to calculate the average salary of employees in each department. Display department_id and average_salary. Use the AVG() function and GROUP BY clause.

3. Write an SQL query to find the highest and lowest salaries in each department. Display department_id, highest_salary, and lowest_salary. Use MAX(), MIN(), and GROUP BY.

4. Write an SQL query to retrieve all departments where the total number of employees is greater than 10. Use the COUNT() function and HAVING clause.

5. Write an SQL query to list all products and their total sales amount, where the total sales amount is greater than 1,000. Use SUM(), GROUP BY, and HAVING clause.

6. Write an SQL query to find the number of orders placed by each customer, only for customers who have placed more than 5 orders. Use COUNT(), GROUP BY, and HAVING.

7. Write an SQL query to display the department names and the total number of employees in each department. Use COUNT(), GROUP BY, and HAVING clause to filter departments with more than 3 employees.

8. Write an SQL query to find the average salary for each department, but only for departments where the average salary is greater than 50,000. Use AVG(), GROUP BY, and HAVING.

9. Write an SQL query to find the total revenue and the number of orders for each product, sorted by total revenue in descending order. Use SUM() and GROUP BY, and apply ORDER BY.

10. Write an SQL query to display the top 3 departments with the highest average salary. Use AVG(), GROUP BY, and ORDER BY with LIMIT (if applicable).

1. What is a subquery in SQL?

2. What are the different types of subqueries?

3. What is a correlated subquery, and how does it differ from a non-correlated subquery?

4. When would you use a subquery instead of a JOIN?

5. Can a subquery return multiple columns?

Query Questions:

1. Write an SQL query to find the names of employees who work in the same department as ‘John Doe’. Assume employees table has employee_name, department_id, and department_id in both tables.

2. Write an SQL query to retrieve all products where the price is greater than the average price of all products. Use a subquery to calculate the average price.

3. Write an SQL query to find customers who have placed orders totaling more than $10,000. Use a subquery to calculate the total amount spent by each customer.

4. Write an SQL query to list employees who earn more than the average salary of their department. Use a correlated subquery.

5. Write an SQL query to find all departments that have more employees than the department with the fewest employees. Use a subquery to find the smallest employee count.

6. Write an SQL query to get the name of employees who have not received any bonuses. Assume employees table and bonuses table are related through employee_id.

7. Write an SQL query to find all orders with the maximum order amount. Use a subquery to find the highest order amount.

8. Write an SQL query to find employees who are managers and have more than 5 direct reports. Assume employees table has manager_id and employee_id.

9. Write an SQL query to get all customers who have not placed any orders. Use a subquery with NOT EXISTS.

10. Write an SQL query to retrieve products that have not been ordered. Use a subquery to find products with no orders.

1. What is a view in SQL?

2. How do you create a view in SQL?

3. What are the advantages of using views?

4. What is the difference between a view and a table?

5. What is an index in SQL?

6. How do you create an index in SQL?

Query Questions:

1. Write an SQL query to create a view that displays the names and salaries of employees from the employees table who work in the ‘Sales’ department.

2. Write an SQL query to retrieve data from a view called TopCustomers that shows customer names and their total purchase amounts. Assume the view was created as follows:

3. Write an SQL query to drop a view named OldOrders.

4. Write an SQL query to update a view named EmployeeSalaries to increase all salaries by 10%. Assume the view is defined as:

5. Write an SQL query to create a view HighSalaryEmployees that lists employees with salaries greater than $75,000.

6. Write an SQL query to create an index on the email column of the users table.

7. Write an SQL query to drop an index named idx_order_date from the orders table.