1.
CREATE TABLE with any two constraints
CREATE TABLE User (
u_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);
CREATE TABLE Product (
p_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) CHECK (price > 0),
description TEXT
);
2. ALTER TABLE
ALTER TABLE User
ADD phone_number VARCHAR(15);
ALTER TABLE Product
MODIFY price DECIMAL(12, 2);
3. DELETE TABLE
DROP TABLE Cart;
4. COMMIT ,SAVEPOINT and ROLLBACK
BEGIN;
INSERT INTO User (u_id, name, email) VALUES (101, 'Alice', 'alice@[Link]');
SAVEPOINT savepoint1;
INSERT INTO User (u_id, name, email) VALUES (102, 'Bob', 'bob@[Link]');
ROLLBACK TO savepoint1;
COMMIT;
5. selecting specific column,
SELECT name, email FROM User;
6. arithmetic operation in select,
SELECT price * 1.05 AS price_with_tax FROM Product;
7. use Column alias ,
SELECT name AS product_name, price AS product_price FROM Product;
8. concatenation operation ,
SELECT name || ' - ' || description AS product_info FROM Product;
9. Literal Character String in select clause
SELECT name, 'is available' AS availability FROM Product;
10. Removing duplicate rows, displaying table structure
SELECT DISTINCT name FROM Product;
DESC User;
11. Using the WHERE Clause
SELECT * FROM Product WHERE price > 100;
12. Character Strings and Dates
SELECT * FROM Order WHERE order_date = '2023-01-01';
13. Using the BETWEEN AND Condition
SELECT * FROM Product WHERE price BETWEEN 50 AND 200;
14. Using the IN Condition
SELECT * FROM User WHERE name IN ('Alice', 'Bob', 'Charlie');
15. Using the LIKE Condition
SELECT * FROM Product WHERE name LIKE 'S%';
16. Using the NULL Conditions
SELECT * FROM Product WHERE description IS NULL;
17. Using any two Logical Conditions
SELECT * FROM Product WHERE price > 50 AND price < 200;
SELECT * FROM User WHERE name = 'Alice' OR email = 'alice@[Link]';
18. Using the ORDER BY Clause
SELECT * FROM Product ORDER BY price DESC;
19. using any 5 Character Functions
SELECT UPPER(name) FROM User;
SELECT LOWER(name) FROM User;
SELECT LENGTH(name) FROM User;
SELECT SUBSTR(name, 1, 3) FROM User;
SELECT REPLACE(name, 'a', 'o') FROM User;
20. Using 3 Number Functions
SELECT ROUND(price, 2) FROM Product;
SELECT FLOOR(price) FROM Product;
SELECT MOD(price, 10) FROM Product;
21. Using any 3 Date Functions
SELECT SYSDATE FROM dual;
SELECT ADD_MONTHS(order_date, 3) FROM Order;
SELECT MONTHS_BETWEEN(SYSDATE, order_date) FROM Order;
22. using Explicit Data Type Conversion
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM Order;
SELECT TO_NUMBER('100') + 50 FROM dual;
SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD') FROM dual;
23. Using NVL Function
SELECT NVL(description, 'No description') FROM Product;
24. using any 4 Group Functions
SELECT COUNT(*) FROM Product;
SELECT AVG(price) FROM Product;
SELECT MAX(price) FROM Product;
SELECT SUM(price) FROM Product;
25. Using the GROUP BY Clause
SELECT u_id, COUNT(*) FROM Order GROUP BY u_id;
26. Using the HAVING Clause
SELECT u_id, COUNT(*) FROM Order GROUP BY u_id HAVING COUNT(*) > 1;
27. Retrieving Records with any equi-joins
SELECT [Link], Order.order_no FROM User JOIN Order ON User.u_id = Order.u_id;
28. Retrieving Records with any non equi-joins or outer join
SELECT [Link], Order.order_no FROM User LEFT JOIN Order ON User.u_id =
Order.u_id;
29. Using a Subquery to Solve a Problem
SELECT * FROM Product WHERE price > (SELECT AVG(price) FROM Product);
30. Multiple-Row Subqueries
SELECT name FROM User WHERE u_id IN (SELECT u_id FROM Order WHERE
order_amount > 100);
31. queries on Set Operators -union
SELECT name FROM User
UNION
SELECT name FROM Product;
32. queries on Set Operators -Intersect
SELECT name FROM User
INTERSECT
SELECT name FROM Product;
33. queries on Set Operators -Minus
SELECT name FROM User
MINUS
SELECT name FROM Product;
34. INSERT Statement
INSERT INTO User (u_id, name, email) VALUES (103, 'David', 'david@[Link]');
35. UPDATE Statement
UPDATE Product SET price = price * 1.1 WHERE name = 'Laptop';
36. DELETE Statement
DELETE FROM User WHERE name = 'David';
37. PL/SQL
BEGIN
INSERT INTO User (u_id, name, email) VALUES (104, 'Eve', 'eve@[Link]');
COMMIT;
END;
38. Create any 3 views
CREATE VIEW UserOrders AS
SELECT User.u_id, [Link], Order.order_no, Order.order_amount
FROM User
JOIN Order ON User.u_id = Order.u_id;
CREATE VIEW HighPriceProducts AS
SELECT * FROM Product WHERE price > 500;
CREATE VIEW OrderSummary AS
SELECT order_no, COUNT(*) AS product_count
FROM Order
GROUP BY order_no;
39. create Sequence
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
40. Create Index
CREATE INDEX idx_product_name ON Product(name);
41. Use Implicit cursor
BEGIN
FOR rec IN (SELECT * FROM User) LOOP
DBMS_OUTPUT.PUT_LINE([Link] || ' - ' || [Link]);
END LOOP;
END;
42. Use Explicit cursor
DECLARE
CURSOR user_cur IS SELECT * FROM User;
user_rec user_cur%ROWTYPE;
BEGIN
OPEN user_cur;
LOOP
FETCH user_cur INTO user_rec;
EXIT WHEN user_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(user_rec.name);
END LOOP;
CLOSE user_cur;
END;
43. Use for iterative statement in PLSQL
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
END;
44. Use Loop statement in PLSQL
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i > 5;
DBMS_OUTPUT.PUT_LINE('Loop iteration: ' || i);
i := i + 1;
END LOOP;
END;
45. Use inbuilt exception
DECLARE
num NUMBER := 0;
BEGIN
num := 10 / num;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
END;
46. Use User defined exception
DECLARE
e_invalid_value EXCEPTION;
value NUMBER := -1;
BEGIN
IF value < 0 THEN
RAISE e_invalid_value;
END IF;
EXCEPTION
WHEN e_invalid_value THEN
DBMS_OUTPUT.PUT_LINE('Invalid value provided');
END;
47. write 2 procedure
CREATE PROCEDURE add_user(u_id IN INT, name IN VARCHAR, email IN VARCHAR) IS
BEGIN
INSERT INTO User (u_id, name, email) VALUES (u_id, name, email);
END;
CREATE PROCEDURE delete_user(u_id IN INT) IS
BEGIN
DELETE FROM User WHERE u_id = u_id;
END;
48. write 2 function
1)CREATE OR REPLACE FUNCTION get_user_total_orders(u_id_in INT)
RETURN NUMBER IS
total_amount NUMBER := 0;
BEGIN
SELECT SUM(order_amount)
INTO total_amount
FROM Order
WHERE u_id = u_id_in;
RETURN NVL(total_amount, 0);
END;
For execution:-
SELECT get_user_total_orders(101) FROM dual;
2)
CREATE OR REPLACE FUNCTION get_category_product_count(c_id_in INT)
RETURN NUMBER IS
product_count NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO product_count
FROM Product
WHERE c_id = c_id_in;
RETURN product_count;
END;
For execution:-
SELECT get_category_product_count(1) FROM dual;
49. write trigger
CREATE OR REPLACE TRIGGER trg_update_tracking_status
AFTER INSERT ON Order
FOR EACH ROW
BEGIN
INSERT INTO Tracking_Detail (t_id, order_no, status)
VALUES (tracking_detail_seq.NEXTVAL, :NEW.order_no, 'Processing');
END;