Datatype: Text, Integer, Decimal
Constraints: Primary key, Foreign key, Default, Not null, Check, Unique, Autoincrement
DDL: CREATE, ALTER, DROP
DML: INSERT, UPDATE, DELETE, SELECT
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS marks;
CREATE TABLE students (
sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
class INTEGER NOT NULL CHECK (class BETWEEN 0 AND 12),
roll INTEGER UNIQUE NOT NULL,
name TEXT NOT NULL CHECK (name <> 'invalid'));
INSERT INTO students (sid, class, roll, name) VALUES ('S001', 10, 101, 'Alice');
INSERT INTO marks (mid, M1, m2) VALUES ('S001', 85.5, 92);
DELETE FROM marks WHERE mid = 1;
INSERT INTO marks (mid, M1, m2) VALUES (1, 85.5, 92);
1.SELECT s.*, m.m1, m.m2 FROM students s INNER JOIN marks m ON s.id = m.student_id;
2.SELECT s.*, m.*FROM students s INNER JOIN marks m ON s.id = m.student_id;
3.SELECT s.id AS student_id, s.roll, s.name, m.m1,m.m2, m.id AS mark_id FROM students s INNER JOIN marks m ON s.id = m.student_id;
4.SELECT s.name, m.m1, m.m2 FROM students s LEFT JOIN marks m ON s.id = m.student_id;
CREATE TABLE t AS SELECT *FROM t';
CREATE VIEW v(c,c') AS SELECT c,c' FROM t;
CREATE INDEX i ON t (c,c');
CREATE UNIQUE INDEX i ON t(c,c');
CREATE /MODIFY TRIGGER tri
[BEFORE/AFTER] [INSERT/UPPER/DELETE]
TRIGGER_TYPE[FOR EACH ROW/FOR EACH STATEMENT]
EXECUTE stored_procedured;
ALTER TABLE t
[ADD c t/ DROP COLUMN c];
[ADD/DROP constraints];
RENAME [TO tt/ c TO cc];
DROP TABLE VIEW /INDEX /TRIGGER <name>;
INSERT INTO t (columns) VALUES (value),(value);
INSERT INTO t1 (columns) SELECT (columns) FROM t2/ Where<match order/datatype with condition>;
UPDATE t SET c1=v1, c2=v2/ Where condition;
DELETE FROM t/Where condition;
SELECT:
Select All from table t.
Select Specific c from table t.
Select Multiple c,c' from table t.
Select with Alias c from t, renaming column in output to cc.
Select with DISTINCT unique (non-duplicate) values of column c from table t.
Select with Expression c and calculated value (c+1) from t.
SELECT */c/c' AS cc /DISTINCT c/c+1 AS c' FROM t;
Select with WHERE rows value in c equals 'value'.
SELECT * FROM t WHERE c = 'v';
Select with Multiple Conditions rows where c equals 'value' AND c' is greater than 10.
SELECT * FROM t WHERE c = 'value' AND c' > 10;
Select with LIKE rows where c contains the substring 'pat'.
SELECT * FROM t WHERE c LIKE '%aha%';
Select with ORDER BY all rows and sorts by c in ASC (default).
SELECT * FROM t ORDER BY c;
Select with DESC all rows and sorts by c in descending order.
SELECT * FROM t ORDER BY c DESC;
Select with LIMIT only the first 10 rows from table t.
SELECT * FROM t LIMIT 10;
Count Rows in table t. Count Distinct unique values in c. Sum calculates the sum of values in column c. Avg/Min/Max Calculates the average, minimum, or maximum value of c.
SELECT COUNT(*), COUNT(DISTINCT c, SUM(c), AVG(c), MIN(c'), MAX(c) FROM t;
Group By groups rows on values in c and calculates the count for each group. Group By HAVING groups by c and filters the groups, only showing groups where the count is greater than 1.
SELECT c, COUNT(*) FROM t GROUP BY c /(HAVING COUNT(*) > 1);
INNER JOIN retrieves rows that have matching values in both tables, joining on columns c from t and c' from t'. LEFT JOIN retrieves all rows from the left table (t) and the matched rows from the right table (t'). RIGHT JOIN retrieves all rows from the right table (t') and matched rows from left table (t). FULL (OUTER) JOIN retrieves all rows when there is a match in one of the tables.
SELECT t.c, t'.c'/* FROM t INNER/LEFT/RIGHT/FULL(OUTER) JOIN t' ON t.c = t'.c';
Subquery retrieves rows from t where the value in c is present in the set of c' values from t'.
SELECT *FROM t WHERE c IN (SELECT c' FROM t');
Subquery in FROM Treats result of inner SELECT on t' as temporary (sub) to query from.
SELECT c FROM (SELECT c' AS c FROM t') AS sub;
1. Multiple Choice Questions
a. Which of the following is NOT a function of a DBMS? iii. Word processing
b. Which type of key uniquely identifies each record in a table? iii. Primary Key
c. What is the primary purpose of a foreign key? iii. To establish and enforce relationships between tables.
d. In a one-to-many relationship between teachers and classes, which statement is true? ii. Each teacher can teach multiple classes, and each class has only one teacher.
e. Which SQL command is used to add new rows to a table? iii. INSERT
f. Which SQL command is used to modify existing data in a table? ii. UPDATE
g. Which SQL clause is used to specify conditions for data retrieval? iii.WHERE
h. What does the SQL LIKE clause help you do? ii. Search for patterns in data.
i. Which SQL data type is used for storing only date values (e.g., YYYY-MM-DD)? iv. DATE
j. Which SQL data type is suitable for storing names and addresses, allowing for variable length? iv. VARCHAR
k. What is the primary difference between CHAR(n) and VARCHAR(n) data types? ii. CHAR has a fixed length, while VARCHAR has a variable length up to n characters.
2. Short Questions
a. Define data and information.
Data: Raw, unprocessed facts, figures, or symbols without context (e.g., 35).
Information: Processed, organized data structured to provide meaning and context (e.g., Temperature: 35°C).
b. What is primary key?
A column or set of columns that uniquely identifies each record in a table. It cannot contain NULL or duplicate values.
c. What is the foreign key?
A column in one table that references the Primary Key of another table, establishing a logical link between them.
d. Define fields and rows.
Fields (Columns): Attributes that define specific data types within a table.
Rows (Records): Single horizontal entries representing a complete set of related data.
e. Differentiate database and DBMS.
Database: Structured collection of data stored electronically.
DBMS: Software application used to define, manipulate, retrieve, and manage database data.
f. Describe RDBMS.
A Relational Database Management System (RDBMS) organizes data into tables (relations) linked by key constraints, guaranteeing structural relationships and referential integrity.
g. Why is a DBMS considered more advantageous for managing large amounts of data compared to a simple spreadsheet?
DBMS manages complex data relationships, supports multi-user concurrency, maintains strict data integrity, provides robust security permissions, and handles high volume without performance degradation.
h. Describe the purpose of primary and foreign keys in a relational database.
Primary Key: Prevents duplicate entries within a table.
Foreign Key: Enforces referential integrity by ensuring valid relational connections between tables.
i. Explain the concept of a one-to-many relationship and provide a real-world example.
Concept: One parent record in Table A connects to multiple child records in Table B, but each record in Table B connects to only one record in Table A.
Example: A Customer can place multiple Orders, but each Order belongs to only one Customer.
j. Briefly describe the roles of DDL and DML in SQL.
DDL (Data Definition Language): Defines database structure and schema (CREATE, ALTER, DROP).
DML (Data Manipulation Language): Manages data stored within tables (INSERT, UPDATE, DELETE, SELECT).
k. Explain the function of the WHERE clause in a SELECT statement.
Filters target dataset records so that only rows meeting specified conditions are returned.
l. What are SQL constraints? Why are they important for maintaining data integrity?
Rules enforced on database columns (NOT NULL, UNIQUE, CHECK) ensuring data remains accurate, valid, and reliable.
m. What is a composite key, and when might it be necessary to use one?
A primary key made of two or more columns combined. It is used when no single column individually guarantees unique row identification.
3. Long Questions
a. Define DBMS with its advantages.
A Database Management System (DBMS) is software designed to define, store, manage, and manipulate structured data safely and efficiently.
Reduced Redundancy: Avoids unnecessary data duplication across tables.
Data Consistency: Propagates changes across linked views to prevent contradictory records.
Enhanced Security: Implements fine-grained access control policies.
Multi-User Access: Manages concurrent user interactions safely.
Automated Backup & Recovery: Protects data against hardware and software crashes.
b. What is the importance of primary key in db? List out its features.
Primary keys provide deterministic row retrieval, enable relational indexing, and establish targets for relational foreign keys.
Uniqueness: Guarantees distinct values across every record.
Non-Nullability: Rejects NULL entries.
Single Constraint: Strictly one primary key constraint permitted per table.
Immutability: Primary key values should remain fixed.
c. Differentiate between DDL and DML.
| Feature | DDL (Data Definition Language) | DML (Data Manipulation Language) |
| Focus | Database structure/schema | Data inside tables |
| Commands | CREATE, ALTER, DROP, TRUNCATE | INSERT, UPDATE, DELETE, SELECT |
| Commit | Auto-committed | Manual commit/rollback options |
d. Show difference between Table and Query.
| Feature | Table | Query |
| Definition | Physical structure containing data | Request executed against tables for specific data |
| Storage | Persistently saved on disk | Dynamic result generated in memory |
| Source | Direct entry point | Derived from one or more base tables |
e. What is the relationship in DBMS? Explain its types.
A relationship connects records across different tables based on matching key columns.
One-to-One (1:1): Each record in Table A connects to one record in Table B (e.g., User and Profile).
One-to-Many (1:N): A record in Table A connects to multiple records in Table B (e.g., Class and Students).
Many-to-Many (M:N): Multiple records in Table A map to multiple records in Table B via a junction table (e.g., Courses and Students).
f. What is a Query? List out its importance in DBMS.
A query is a structured request to fetch or modify database contents based on conditional criteria.
Extracts precise records from massive datasets efficiently.
Executes bulk data updates or removals safely.
Aggregates key fields (e.g., SUM, AVG, COUNT).
Combines multi-table datasets through SQL joins.
g. What is a report in the context of MySQL and what is its purpose?
A report is a structured, formatted output generated from query results, designed for human review and presentation. Its purpose is to deliver summaries, audit trails, and data visual insights to stakeholders.
h. Describe report with its features.
Reports aggregate complex raw SQL data into structured documents featuring summary fields, organized sections, and headers/footers.
Summarization: Computes grouped sums, totals, and averages.
Clean Layout: Presents headers, footers, page numbering, and visual hierarchies.
Data Selection: Filters focus down strictly to actionable, relevant metrics.
4. SQL Queries
a. Create database:
CREATE DATABASE CompanyData;
b. Create table:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2));
c. Add column:
ALTER TABLE Employees
ADD Email VARCHAR(100);
d. Modify default value:
ALTER TABLE Employees
ALTER COLUMN Salary SET DEFAULT 25000;
e. Rename table:
RENAME TABLE Employees TO StaffMembers;
f. Insert new record:
INSERT INTO StaffMembers (EmployeeID, FirstName, LastName, Salary)
VALUES (101, 'Sampada', 'Bhattrai', 30000.50);
g. Retrieve all columns and rows:
SELECT * FROM StaffMembers;
h. Retrieve specific columns:
SELECT FirstName, Salary FROM StaffMembers;
i. Increase salary by 10%:
UPDATE StaffMembers
SET Salary = Salary * 1.10;
j. Delete specific record:
DELETE FROM StaffMembers
WHERE EmployeeID = 103;
k. Delete all records keeping table structure:
DELETE FROM StaffMembers;