SQL & DBMS Internal Exam Quick Notes
SQL Quick Reference Notes
1. CREATE DATABASE & TABLE
CREATE DATABASE bankdetails;
CREATE TABLE library (
book_id INT PRIMARY KEY,
book_nm VARCHAR(25),
stud_id INT
);
2. INSERT DATA
INSERT INTO library VALUES (101, 'SQL Server', 1);
INSERT INTO Users (FirstName, LastName, DateOfBirth, Email, City)
VALUES ('John', 'Smith', '1969-12-12', 'john@here.com', 'New York');
3. SELECT DATA
SELECT * FROM Users;
SELECT * FROM Users WHERE City = 'New York';
SELECT * FROM Users WHERE City = 'New York' AND LastName = 'Smith';
4. WHERE, AND, OR, BETWEEN, IN
SELECT * FROM sales WHERE OrderQuantity > 2 AND CustomerName = 'Baldwin';
SELECT * FROM sales WHERE OrderDate BETWEEN '2024-07-20' AND '2024-08-05';
SELECT * FROM Users WHERE CustomerName IN ('Smith', 'Grant');
5. DISTINCT
SELECT DISTINCT City FROM Users;
6. AGGREGATE FUNCTIONS
SELECT COUNT(*) FROM Sales;
SQL & DBMS Internal Exam Quick Notes
SELECT SUM(OrderPrice) FROM Sales;
SELECT AVG(OrderQuantity) FROM Sales;
SELECT MIN(OrderPrice) FROM Sales;
SELECT MAX(OrderPrice) FROM Sales;
7. ORDER BY
SELECT * FROM Users ORDER BY FirstName;
SELECT * FROM Users ORDER BY LastName, Age;
SELECT * FROM Users ORDER BY FirstName DESC;
8. UPDATE STATEMENT
UPDATE Sales SET OrderQuantity = 6 WHERE OrderId = 1;
UPDATE Sales SET OrderQuantity = OrderQuantity + 4 WHERE OrderId = 1;
UPDATE Sales SET OrderQuantity = 2, OrderPrice = 160 WHERE OrderId = 1;
9. GROUP BY & HAVING
SELECT CustomerName, SUM(OrderPrice) FROM Sales GROUP BY CustomerName;
SELECT CustomerName, SUM(OrderPrice) FROM Sales GROUP BY CustomerName HAVING SUM(OrderPrice) <
2000;
10. DELETE, TRUNCATE, DROP
DELETE FROM Sales WHERE CustomerName = 'Smith';
TRUNCATE TABLE Sales;
DROP TABLE Sales;
DROP DATABASE mydatabase;
11. BETWEEN, IN, NOT IN
SELECT * FROM sales WHERE OrderDate BETWEEN '2024-07-20' AND '2024-08-05';
SELECT * FROM Users WHERE CustomerName IN ('Smith', 'Grant');
SELECT * FROM Inventory WHERE Product NOT IN (SELECT Product FROM Orders WHERE Customer =
'A+Maintenance');
SQL & DBMS Internal Exam Quick Notes
12. LIKE OPERATOR
SELECT CustomerName FROM Sales WHERE CustomerName LIKE 'S%';
SELECT * FROM Users WHERE City LIKE '%ewy%';
SELECT * FROM Users WHERE City NOT LIKE '%ewy%';
13. SQL JOINS
Types: JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
LEFT JOIN:
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons P
LEFT JOIN Orders O ON P.P_Id = O.P_Id;
RIGHT JOIN:
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons P
RIGHT JOIN Orders O ON P.P_Id = O.P_Id;
FULL JOIN:
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons P
FULL JOIN Orders O ON P.P_Id = O.P_Id;
14. COMMON QUESTIONS
Q: How to create a table?
A: CREATE TABLE table_name (col1 datatype, col2 datatype);
Q: What is the LEFT JOIN command?
A: Combines all rows from left table and matched rows from right table.
SQL & DBMS Internal Exam Quick Notes
Q: How to use GROUP BY?
A: Used with aggregate functions to group rows by column values.
Q: What does the HAVING clause do?
A: Filters grouped records (used with GROUP BY).
Q: Difference between DELETE and TRUNCATE?
A: DELETE removes rows with condition, TRUNCATE removes all rows faster, no WHERE.
