1. Write a SQL query to insert new record in employes table?
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id)
VALUES (101, 'John', 'Doe', 'john.doe@example.com', '123-456-7890', '2024-10-01', 'IT_PROG', 60000, 10);
2. Write a query to update the quantity column in products to 10?
UPDATE products SET quantity = 50 WHERE product_id = 101;
3. Write a SQL query to delete all the records from the orders table where the status is cancelled?
DELETE FROM orders WHERE status = 'cancelled';
4. Write a SQL query to retreive all the columns from the product table where the category is electronics and the price is less than 1000?
SELECT * FROM product WHERE category = 'electronics' AND price < 1000;
5. Write a SQL query to retreive names of all the customers in alphabetic oder?
SELECT customer_name FROM customers ORDER BY customer_name ASC;
6. Write a SQL query to retreive the total no of orders from orders table?
SELECT COUNT(*) AS total_orders FROM orders;
7. Write a SQL query to retrieve the product name, category, and supplier name from the "products", "categories", and "suppliers" tables, joining them on the appropriate columns.?
SELECT p.product_name, c.category_name, s.supplier_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN suppliers s ON p.supplier_id = s.supplier_id;
8. Write a SQL query to retrieve the employee name and department name from the "employees" and "departments" tables, joining them on the "department_id" column.?
SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
9. Write a SQL query to retrieve the customer name and order amount from the "customers" and "orders" tables, joining them on the "customer_id" column, and only including orders with amounts greater than 1000.?
SELECT c.customer_name, o.order_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_amount > 1000;
10. Write a SQL query to retrieve the average price of products in each category from the "products" table.?
SELECT category_id, AVG(price) AS average_price FROM products GROUP BY category_id;
11. Write a SQL query to retrieve the maximum salary for each department from the "employees" table.?
SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id;
12. Write a SQL query to retrieve the total revenue generated by each customer from the "orders" and "order_items" tables.
SELECT o.customer_id, SUM(oi.quantity * oi.price) AS total_revenue FROM orders o JOIN order_items oi ON o.order_id = oi.order_id GROUP BY o.customer_id;
13. Write a SQL query to delete all records from the "customers" table where the "last_login_date" is older than 1 year?
DELETE FROM customers WHERE last_login_date < NOW() - INTERVAL 1 YEAR;
14. Write a SQL query to update the "discount" column of the "orders" table by increasing it by 5% for all orders placed before a specific date.
UPDATE orders SET discount = discount * 1.05 WHERE order_date < '2023-01-01';
15. Write a SQL query to retrieve all the products with a price either above 1000 or below 500.
SELECT * FROM products WHERE price > 1000 OR price < 500;
16. Write a SQL query to retrieve the employees who were hired between a specific date range?
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
17. Write a SQL query to retrieve all the customers who do not have a phone number specified in the database.?
SELECT * FROM customers WHERE phone_number IS NULL OR phone_number = '';
18. Write a SQL query to retrieve the current date and time.?
SELECT NOW() AS current_datetime;
19. Write a SQL query to retrieve the uppercase names of all the employees from the "employees" table.?
SELECT UPPER(first_name) AS first_name, UPPER(last_name) AS last_name FROM employees;
20. Write a SQL query to retrieve the average price of products after applying a 10% discount from the "products" table.?
SELECT AVG(price * 0.9) AS average_discounted_price FROM products;
21. Create a view named "order_summary" that retrieves the total order amount and the number of orders for each customer from the "orders" table.
CREATE VIEW order_summary AS SELECT customer_id, SUM(order_amount) AS total_order_amount, COUNT(order_id) AS number_of_orders FROM orders GROUP BY customer_id;
22. Create an index on the "email" column of the "customers" table for faster searching.?
CREATE INDEX idx_email ON customers(email);
23. Create a view named "product_inventory" that retrieves the product name and the available quantity for each product from the "products" and "inventory" tables.
CREATE VIEW product_inventory AS SELECT p.product_name, i.available_quantity FROM products p JOIN inventory i ON p.product_id = i.product_id;
24. Write a SQL query to start a transaction, delete all records from the "orders" table, and roll back the transaction.?
START TRANSACTION; DELETE FROM orders; ROLLBACK;
25. Write a SQL query to update the "balance" column of the "accounts" table by adding a specific amount for a specific account, ensuring the consistency of the transaction.?
START TRANSACTION; UPDATE accounts SET balance = balance + 100 WHERE account_id = 'SPECIFIC_ACCOUNT_ID'; COMMIT;
26. Write a SQL query to lock a specific row in the "employees" table to prevent other transactions from modifying it.?
SELECT * FROM employees WHERE employee_id = 'SPECIFIC_EMPLOYEE_ID' FOR UPDATE;
