Scribd
Upload
37 views16 pages

MySQL Lab Manual: SQL Basics & Joins

The MySQL Practical Lab Manual provides comprehensive instructions on various SQL commands and operations, including data retrieval with SELECT statements, filtering with WHERE clauses, and sorting with ORDER BY. It covers JOIN operations, database creation, table management, and data manipulation techniques such as INSERT, UPDATE, and DELETE. Additionally, it explains aggregate functions and the use of GROUP BY and HAVING clauses for data analysis.

Uploaded by

betadeku3
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
37 views16 pages

MySQL Lab Manual: SQL Basics & Joins

The MySQL Practical Lab Manual provides comprehensive instructions on various SQL commands and operations, including data retrieval with SELECT statements, filtering with WHERE clauses, and sorting with ORDER BY. It covers JOIN operations, database creation, table management, and data manipulation techniques such as INSERT, UPDATE, and DELETE. Additionally, it explains aggregate functions and the use of GROUP BY and HAVING clauses for data analysis.

Uploaded by

betadeku3
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MySQL Practical Lab Manual-PART 1

1. SELECT all columns or specific columns


The SELECT statement is used to retrieve data from one or more tables. You can select all
columns using '*' or specific columns by naming them.

Syntax:

SELECT column1, column2 FROM table_name;


SELECT * FROM table_name;

Examples:

 SELECT * FROM CUSTOMER;


 SELECT CUS_LNAME, CUS_FNAME FROM CUSTOMER;
 SELECT EMP_LNAME, EMP_FNAME FROM EMP;
 SELECT * FROM PRODUCT;
 SELECT P_CODE, P_DESCRIPT FROM PRODUCT;

2. SELECT using WHERE clause


The WHERE clause is used to filter records that fulfill a specified condition.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Examples:

 SELECT * FROM CUSTOMER WHERE CUS_BALANCE > 200;


 SELECT * FROM EMP WHERE EMP_TITLE = 'Mr.';
 SELECT * FROM PRODUCT WHERE P_PRICE > 100;
 SELECT * FROM EMPLOYEE WHERE EMP_YEARS >= 10;
 SELECT * FROM CUSTOMER WHERE CUS_LNAME = 'Smith';

3. Using AND, OR, NOT


Logical operators are used to combine multiple conditions in a WHERE clause.

Syntax:
SELECT column1 FROM table WHERE condition1 AND condition2;
SELECT column1 FROM table WHERE condition1 OR condition2;
SELECT column1 FROM table WHERE NOT condition;

Examples:

 SELECT * FROM EMP WHERE EMP_TITLE = 'Mr.' AND EMP_AREACODE = '615';


 SELECT * FROM CUSTOMER WHERE CUS_BALANCE > 300 OR CUS_LNAME = 'Smith';
 SELECT * FROM EMPLOYEE WHERE NOT EMP_YEARS < 10;
 SELECT * FROM PRODUCT WHERE P_PRICE > 100 AND P_QOH > 10;
 SELECT * FROM EMP WHERE EMP_TITLE = 'Ms.' OR EMP_TITLE = 'Mrs.';

4. ORDER BY
The ORDER BY clause is used to sort the result-set in ascending or descending order.

Syntax:

SELECT column1 FROM table ORDER BY column1 ASC|DESC;

Examples:

 SELECT * FROM CUSTOMER ORDER BY CUS_LNAME ASC;


 SELECT * FROM EMPLOYEE ORDER BY EMP_YEARS DESC;
 SELECT * FROM PRODUCT ORDER BY P_PRICE DESC;
 SELECT * FROM VENDOR ORDER BY V_NAME ASC;
 SELECT * FROM EMP ORDER BY EMP_HIRE_DATE ASC;

5. >, >=, <, <=, != (Not equal to)


Comparison operators are used to compare values in a WHERE clause.

Syntax:

SELECT * FROM table WHERE column > value;


SELECT * FROM table WHERE column != value;

Examples:

 SELECT * FROM PRODUCT WHERE P_PRICE > 100;


 SELECT * FROM EMPLOYEE WHERE EMP_YEARS >= 10;
 SELECT * FROM CUSTOMER WHERE CUS_BALANCE < 300;
 SELECT * FROM PRODUCT WHERE P_DISCOUNT != 0;
 SELECT * FROM EMP WHERE EMP_NUM <= 110;

6. BETWEEN
The BETWEEN operator is used to filter values within a specified range.

Syntax:

SELECT column1 FROM table WHERE column BETWEEN value1 AND value2;

Examples:

 SELECT * FROM EMPLOYEE WHERE EMP_YEARS BETWEEN 10 AND 15;


 SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 10 AND 50;
 SELECT * FROM CUSTOMER WHERE CUS_BALANCE BETWEEN 100 AND 500;
 SELECT * FROM EMP WHERE EMP_NUM BETWEEN 105 AND 110;
 SELECT * FROM LINE WHERE LINE_UNITS BETWEEN 1 AND 3;

7. IN
The IN operator allows you to specify multiple values in a WHERE clause.

Syntax:

SELECT column1 FROM table WHERE column IN (value1, value2, ...);

Examples:

 SELECT * FROM EMP WHERE EMP_TITLE IN ('Mr.', 'Ms.');


 SELECT * FROM PRODUCT WHERE V_CODE IN (21344, 24288);
 SELECT * FROM CUSTOMER WHERE CUS_LNAME IN ('Smith', 'Brown');
 SELECT * FROM EMPLOYEE WHERE EMP_AREACODE IN ('615', '901');
 SELECT * FROM VENDOR WHERE V_STATE IN ('TN', 'FL');

8. LIKE
The LIKE operator is used for pattern matching using wildcards (% and _).

Syntax:

SELECT column1 FROM table WHERE column LIKE pattern;

Examples:
 SELECT * FROM CUSTOMER WHERE CUS_LNAME LIKE 'S%';
 SELECT * FROM PRODUCT WHERE P_DESCRIPT LIKE '%blade%';
 SELECT * FROM EMP WHERE EMP_FNAME LIKE '__orge';
 SELECT * FROM EMPLOYEE WHERE EMP_PHONE LIKE '324%';
 SELECT * FROM VENDOR WHERE V_NAME LIKE '%Inc%';

9. LIMIT
The LIMIT clause is used to specify the number of records to return.

Syntax:

SELECT column1 FROM table LIMIT number;

Examples:

 SELECT * FROM CUSTOMER LIMIT 5;


 SELECT * FROM EMP LIMIT 10;
 SELECT * FROM PRODUCT ORDER BY P_PRICE DESC LIMIT 3;
 SELECT * FROM VENDOR LIMIT 4;
 SELECT * FROM EMPLOYEE LIMIT 2;

10. HAVING
The HAVING clause is used to filter results after aggregation, unlike WHERE which filters
before aggregation.

Syntax:

SELECT column, AGG_FUNC(column2) FROM table GROUP BY column HAVING


AGG_FUNC(column2) condition;

Examples:

 SELECT EMP_TITLE, COUNT(*) FROM EMP GROUP BY EMP_TITLE HAVING COUNT(*) >
2;
 SELECT V_CODE, AVG(P_PRICE) FROM PRODUCT GROUP BY V_CODE HAVING
AVG(P_PRICE) > 50;
 SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING
SUM(LINE_UNITS) > 3;
 SELECT CUS_CODE, COUNT(*) FROM INVOICE GROUP BY CUS_CODE HAVING COUNT(*)
>= 2;
 SELECT EMP_AREACODE, COUNT(*) FROM EMPLOYEE GROUP BY EMP_AREACODE
HAVING COUNT(*) > 5;

11. GROUP BY
The GROUP BY statement is used with aggregate functions to group result-set by one or
more columns.

Syntax:

SELECT column, AGG_FUNC(column2) FROM table GROUP BY column;

Examples:

 SELECT EMP_TITLE, COUNT(*) FROM EMP GROUP BY EMP_TITLE;


 SELECT V_CODE, AVG(P_PRICE) FROM PRODUCT GROUP BY V_CODE;
 SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE;
 SELECT EMP_AREACODE, COUNT(*) FROM EMPLOYEE GROUP BY EMP_AREACODE;
 SELECT CUS_CODE, COUNT(*) FROM INVOICE GROUP BY CUS_CODE;

12. DISTINCT and ALIAS


DISTINCT removes duplicates; ALIAS gives a temporary name to columns or tables.

Syntax:

SELECT DISTINCT column FROM table;


SELECT column AS alias FROM table;

Examples:

 SELECT DISTINCT EMP_TITLE FROM EMP;


 SELECT EMP_LNAME AS LastName FROM EMP;
 SELECT CUS_LNAME AS Customer FROM CUSTOMER;
 SELECT DISTINCT V_STATE FROM VENDOR;
 SELECT P_DESCRIPT AS Description FROM PRODUCT;

13. MAX, MIN, AVG, COUNT, SUM


Aggregate functions perform calculations on a set of values and return a single value.

Syntax:
SELECT MAX(column) FROM table;
SELECT MIN(column) FROM table;
SELECT AVG(column) FROM table;
SELECT COUNT(column) FROM table;
SELECT SUM(column) FROM table;

Examples:

 SELECT MAX(P_PRICE) FROM PRODUCT;


 SELECT MIN(EMP_YEARS) FROM EMPLOYEE;
 SELECT AVG(CUS_BALANCE) FROM CUSTOMER;
 SELECT COUNT(*) FROM EMP;
 SELECT SUM(LINE_UNITS) FROM LINE;
MySQL Practical Lab Manual: SQL
JOINs-PART 2
1. INNER JOIN
INNER JOIN returns only the rows that have matching values in both tables.

Syntax:

SELECT columns FROM table1 INNER JOIN table2 ON [Link] = [Link];

Examples:

 SELECT CUSTOMER.CUS_LNAME, INVOICE.INV_DATE FROM CUSTOMER INNER JOIN


INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;
 SELECT LINE.P_CODE, PRODUCT.P_DESCRIPT FROM LINE INNER JOIN PRODUCT ON
LINE.P_CODE = PRODUCT.P_CODE;
 SELECT EMP.EMP_LNAME, EMPLOYEE.EMP_PHONE FROM EMP INNER JOIN
EMPLOYEE ON EMP.EMP_NUM = EMPLOYEE.EMP_NUM;
 SELECT PRODUCT.P_DESCRIPT, VENDOR.V_NAME FROM PRODUCT INNER JOIN
VENDOR ON PRODUCT.V_CODE = VENDOR.V_CODE;
 SELECT INVOICE.INV_NUMBER, CUSTOMER.CUS_FNAME FROM INVOICE INNER JOIN
CUSTOMER ON INVOICE.CUS_CODE = CUSTOMER.CUS_CODE;

2. LEFT JOIN
LEFT JOIN returns all records from the left table, and matched records from the right table.
NULLs are returned for unmatched rows.

Syntax:

SELECT columns FROM table1 LEFT JOIN table2 ON [Link] = [Link];

Examples:

 SELECT PRODUCT.P_CODE, VENDOR.V_NAME FROM PRODUCT LEFT JOIN VENDOR ON


PRODUCT.V_CODE = VENDOR.V_CODE;
 SELECT CUSTOMER.CUS_CODE, INVOICE.INV_DATE FROM CUSTOMER LEFT JOIN
INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;
 SELECT EMP.EMP_NUM, EMPLOYEE.EMP_PHONE FROM EMP LEFT JOIN EMPLOYEE ON
EMP.EMP_NUM = EMPLOYEE.EMP_NUM;
 SELECT LINE.INV_NUMBER, PRODUCT.P_DESCRIPT FROM LINE LEFT JOIN PRODUCT
ON LINE.P_CODE = PRODUCT.P_CODE;
 SELECT VENDOR.V_CODE, PRODUCT.P_DESCRIPT FROM VENDOR LEFT JOIN PRODUCT
ON VENDOR.V_CODE = PRODUCT.V_CODE;

3. RIGHT JOIN
RIGHT JOIN returns all records from the right table, and matched records from the left table.
NULLs are returned for unmatched rows.

Syntax:

SELECT columns FROM table1 RIGHT JOIN table2 ON [Link] = [Link];

Examples:

 SELECT PRODUCT.P_CODE, VENDOR.V_NAME FROM PRODUCT RIGHT JOIN VENDOR


ON PRODUCT.V_CODE = VENDOR.V_CODE;
 SELECT INVOICE.INV_NUMBER, CUSTOMER.CUS_FNAME FROM INVOICE RIGHT JOIN
CUSTOMER ON INVOICE.CUS_CODE = CUSTOMER.CUS_CODE;
 SELECT PRODUCT.P_DESCRIPT, LINE.LINE_UNITS FROM LINE RIGHT JOIN PRODUCT
ON LINE.P_CODE = PRODUCT.P_CODE;
 SELECT EMP.EMP_NUM, EMPLOYEE.EMP_PHONE FROM EMP RIGHT JOIN EMPLOYEE
ON EMP.EMP_NUM = EMPLOYEE.EMP_NUM;
 SELECT VENDOR.V_NAME, PRODUCT.P_DESCRIPT FROM PRODUCT RIGHT JOIN
VENDOR ON PRODUCT.V_CODE = VENDOR.V_CODE;

4. FULL OUTER JOIN (Simulated using UNION)


MySQL does not support FULL OUTER JOIN directly, but it can be simulated using UNION of
LEFT JOIN and RIGHT JOIN.

Syntax:

SELECT columns FROM table1 LEFT JOIN table2 ON condition


UNION
SELECT columns FROM table1 RIGHT JOIN table2 ON condition;

Examples:

 SELECT CUSTOMER.CUS_CODE, INVOICE.INV_DATE FROM CUSTOMER LEFT JOIN


INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
UNION
SELECT CUSTOMER.CUS_CODE, INVOICE.INV_DATE FROM CUSTOMER RIGHT JOIN
INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;
 SELECT PRODUCT.P_CODE, VENDOR.V_NAME FROM PRODUCT LEFT JOIN VENDOR ON
PRODUCT.V_CODE = VENDOR.V_CODE
UNION
SELECT PRODUCT.P_CODE, VENDOR.V_NAME FROM PRODUCT RIGHT JOIN VENDOR
ON PRODUCT.V_CODE = VENDOR.V_CODE;
 SELECT EMP.EMP_NUM, EMPLOYEE.EMP_PHONE FROM EMP LEFT JOIN EMPLOYEE ON
EMP.EMP_NUM = EMPLOYEE.EMP_NUM
UNION
SELECT EMP.EMP_NUM, EMPLOYEE.EMP_PHONE FROM EMP RIGHT JOIN EMPLOYEE
ON EMP.EMP_NUM = EMPLOYEE.EMP_NUM;
 SELECT LINE.P_CODE, PRODUCT.P_DESCRIPT FROM LINE LEFT JOIN PRODUCT ON
LINE.P_CODE = PRODUCT.P_CODE
UNION
SELECT LINE.P_CODE, PRODUCT.P_DESCRIPT FROM LINE RIGHT JOIN PRODUCT ON
LINE.P_CODE = PRODUCT.P_CODE;
 SELECT INVOICE.CUS_CODE, CUSTOMER.CUS_LNAME FROM INVOICE LEFT JOIN
CUSTOMER ON INVOICE.CUS_CODE = CUSTOMER.CUS_CODE
UNION
SELECT INVOICE.CUS_CODE, CUSTOMER.CUS_LNAME FROM INVOICE RIGHT JOIN
CUSTOMER ON INVOICE.CUS_CODE = CUSTOMER.CUS_CODE;
MySQL Practical Lab Manual: University
Database-PART 3
1. CREATE DATABASE
This command is used to create a new database in MySQL.

Syntax:

CREATE DATABASE university;

Examples:

 CREATE DATABASE university;

2. USE / SELECT DATABASE


The USE command selects a database to work with.

Syntax:

USE university;

Examples:

 USE university;

3. DROP DATABASE
The DROP DATABASE command deletes a database permanently.

Syntax:

DROP DATABASE university;

Examples:

 DROP DATABASE university;


4. CREATE TABLE
This command creates a table with various constraints like PRIMARY KEY, FOREIGN KEY,
AUTO_INCREMENT, NOT NULL, DEFAULT, and CHECK.

Syntax:

CREATE TABLE course (


courseid INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
duration INT NOT NULL,
cost DECIMAL(10,2) DEFAULT 50000 CHECK (cost >= 10000)
);

Examples:

 CREATE TABLE student (


studentid INT PRIMARY KEY AUTO_INCREMENT,
studentname VARCHAR(50) NOT NULL,
email VARCHAR(50),
county VARCHAR(30),
phonenumber VARCHAR(15),
courseid INT,
FOREIGN KEY(courseid) REFERENCES course(courseid)
);
 CREATE TABLE unit (
unitcode VARCHAR(10) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
duration INT,
courseid INT,
FOREIGN KEY(courseid) REFERENCES course(courseid)
);
 CREATE TABLE enrollment (
studentid INT,
unitcode VARCHAR(10),
mark DECIMAL(5,2),
PRIMARY KEY(studentid, unitcode),
FOREIGN KEY(studentid) REFERENCES student(studentid) ON DELETE CASCADE,
FOREIGN KEY(unitcode) REFERENCES unit(unitcode) ON DELETE CASCADE
);

5. ADD PRIMARY KEY / DROP PRIMARY KEY


Modify existing tables to add or drop primary keys.
Syntax:

ALTER TABLE tablename ADD PRIMARY KEY (column);


ALTER TABLE tablename DROP PRIMARY KEY;

Examples:

 ALTER TABLE course ADD PRIMARY KEY(courseid);


 ALTER TABLE course DROP PRIMARY KEY;

6. ADD COLUMN / DROP COLUMN


Add or remove a column from a table.

Syntax:

ALTER TABLE tablename ADD columnname datatype;


ALTER TABLE tablename DROP COLUMN columnname;

Examples:

 ALTER TABLE student ADD date_of_birth DATE;


 ALTER TABLE student DROP COLUMN county;

7. DESCRIBE TABLE
Shows the structure of a table including column names and data types.

Syntax:

DESCRIBE tablename;

Examples:

 DESCRIBE student;
 DESCRIBE course;

8. DROP TABLE
Deletes a table permanently from the database.

Syntax:
DROP TABLE tablename;

Examples:

 DROP TABLE enrollment;


 DROP TABLE unit;
MySQL Practical Lab Manual: University
Database - Part 4
1. INSERT INTO (One record at a time)
Use the INSERT INTO statement to add a single record into a table.

Syntax:

INSERT INTO table_name VALUES (value1, value2, ...);

Examples:

 INSERT INTO course VALUES (1, 'Computer Science', 4, 80000);


 INSERT INTO unit VALUES ('CS101', 'Introduction to Programming', 60, 1);
 INSERT INTO student VALUES (1, 'John Doe', 'john@[Link]', 'Nairobi',
'0712345678', 1);
 INSERT INTO enrollment VALUES (1, 'CS101', 78.5);

2. INSERT INTO (Bulk insert with listed columns)


Insert multiple records by specifying the column names explicitly.

Syntax:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1,
value2, ...);

Examples:

 INSERT INTO course (courseid, name, duration, cost) VALUES


(2, 'Information Technology', 3, 70000),
(3, 'Software Engineering', 4, 85000);
 INSERT INTO unit (unitcode, name, duration, courseid) VALUES
('CS102', 'Databases', 45, 1),
('IT101', 'Networks', 50, 2);
 INSERT INTO student (studentid, studentname, email, county, phonenumber, courseid)
VALUES
(2, 'Mary Ann', 'mary@[Link]', 'Kiambu', '0723456789', 2),
(3, 'Peter Kim', 'peter@[Link]', 'Mombasa', '0734567890', 3);
3. INSERT INTO (Without specifying column names)
Insert records assuming all columns are filled in the correct order.

Syntax:

INSERT INTO table_name VALUES (...), (...);

Examples:

 INSERT INTO course VALUES (4, 'Data Science', 4, 90000);


 INSERT INTO unit VALUES ('SE201', 'Software Design', 60, 3);
 INSERT INTO student VALUES (4, 'Lucy Maina', 'lucy@[Link]', 'Nakuru',
'0745678901', 3);

4. UPDATE Command
UPDATE is used to modify existing records in a table.

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Examples:

 UPDATE student SET email = '[Link]@[Link]' WHERE studentid = 1;


 UPDATE course SET cost = 75000 WHERE courseid = 2;
 UPDATE unit SET duration = 65 WHERE unitcode = 'CS101';
 UPDATE enrollment SET mark = 85 WHERE studentid = 1 AND unitcode = 'CS101';

5. DELETE Command with ON DELETE CASCADE


DELETE removes records from a table. When used with ON DELETE CASCADE on foreign
keys, related child records are also removed.

Syntax:

DELETE FROM table_name WHERE condition;

Examples:

 DELETE FROM student WHERE studentid = 4; -- Also deletes from enrollment if ON


DELETE CASCADE is set
 DELETE FROM course WHERE courseid = 3; -- Deletes related students and units if
foreign keys have ON DELETE CASCADE
 DELETE FROM unit WHERE unitcode = 'SE201'; -- Removes related enrollment entries if
cascading is set

Common questions

Powered by AI

An INNER JOIN returns rows where there is a match in both tables involved in the join, effectively filtering data to only include records with a common value in the specified columns. This is useful for cases where complete data integrity is necessary for both tables, such as 'SELECT CUSTOMER.CUS_LNAME, INVOICE.INV_DATE FROM CUSTOMER INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;' . In contrast, a LEFT JOIN returns all rows from the left table and matched rows from the right table, filling unmatched right-side columns with NULLs. It is especially useful for queries where preserving all records from the main table is necessary, even if there are no matches in the joined table, like 'SELECT CUSTOMER.CUS_CODE, INVOICE.INV_DATE FROM CUSTOMER LEFT JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;' .

The WHERE and HAVING clauses both filter records, but they differ in their purpose and application. The WHERE clause is used to filter records before any groupings or aggregations have been applied. It applies to individual rows and is used to set conditions on which records to select from a table. For example, 'SELECT * FROM CUSTOMER WHERE CUS_BALANCE > 200;' filters customers with a balance greater than 200 . In contrast, the HAVING clause filters records after grouping and aggregation have occurred. It is used when conditions need to be applied to aggregated data. For example, 'SELECT EMP_TITLE, COUNT(*) FROM EMP GROUP BY EMP_TITLE HAVING COUNT(*) > 2;' selects job titles that appear more than twice after counting them .

Aggregate functions perform calculations on a set of values, returning a single value result that provides insights into the data. The MAX and MIN functions return the highest and lowest values in a column, such as finding the most or least expensive product with 'SELECT MAX(P_PRICE) FROM PRODUCT;' . AVG calculates the average value of a numeric column, like the average customer balance with 'SELECT AVG(CUS_BALANCE) FROM CUSTOMER;' . COUNT tallies the number of records or non-null entries in a column, useful for summarizing datasets, as in 'SELECT COUNT(*) FROM EMP;' to get the total number of employees . SUM adds up numeric column values, offering totals like 'SELECT SUM(LINE_UNITS) FROM LINE;' for inventory assessments . These functions condense large datasets into meaningful summaries for analysis.

Primary keys are unique identifiers for table records, ensuring each record is distinct. They enforce entity integrity by ensuring that no two rows have the same primary key value, as seen in 'CREATE TABLE student ( studentid INT PRIMARY KEY AUTO_INCREMENT, ... )' . Foreign keys link records between tables, creating relationships that preserve referential integrity. They ensure that the data in one table correlates to relevant data in another, enabling complex queries and operations. For example, 'CREATE TABLE enrollment ( studentid INT, ... FOREIGN KEY(studentid) REFERENCES student(studentid) ON DELETE CASCADE, ... );' ensures that student enrollments automatically remove corresponding entries if a student record is deleted . Together, primary and foreign keys provide a robust structure to maintain data accuracy, prevent redundancy, and ensure all data remains interconnected and reliable.

SQL JOINs facilitate comprehensive data retrieval from different tables by integrating related data based on common keys. INNER JOIN selects records with matching keys in both tables, enabling focused and integrity-driven analysis of intersecting datasets, such as 'SELECT CUSTOMER.CUS_LNAME, INVOICE.INV_DATE FROM CUSTOMER INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;' . LEFT JOIN ensures inclusion of all records from the left table and corresponding matches from the right, supporting scenarios where primary table information is central and supplementary data is optional. RIGHT JOIN works oppositely, which is often used when the secondary table's data is crucial, e.g., preserving all vendor details while showing available products. FULL OUTER JOIN, which MySQL simulates through the union of LEFT and RIGHT JOIN, captures all records from both tables, providing the most comprehensive view by including unmatched entries as NULLs, effectively revealing gaps in relational data that need resolution. Using JOINs, databases can simulate complex relational structures, support real-world business scenarios (such as customer orders, employee roles), and refine integration, analysis, and reporting capabilities in diverse, interconnected datasets.

The ALTER TABLE command in SQL provides flexibility to modify an existing database schema to accommodate evolving business requirements without recreating tables. It can add new columns, as shown in 'ALTER TABLE student ADD date_of_birth DATE;' for extending personal details storage . It enables dropping unnecessary columns with 'ALTER TABLE student DROP COLUMN county;' to streamline or refactor storage . ALTER TABLE can also manipulate constraints, such as adding or dropping primary keys for identity management, and FOREIGN KEY constraints to adjust relationships dynamically. Furthermore, changing column data types or default values tunes database design to improve data processing or accommodate enhancements. By facilitating these modifications, ALTER TABLE supports continuous database growth and adaptation, ensuring applications remain aligned with business developments while maintaining performance and structural integrity.

The LIKE operator in SQL provides powerful pattern matching capabilities, enabling flexible and intuitive data filtering that is essential for text-based query requirements. Unlike exact matches, LIKE uses wildcards such as '%' for any sequence of characters and '_' for a single character, offering greater versatility in searches. For instance, 'SELECT * FROM CUSTOMER WHERE CUS_LNAME LIKE 'S%';' retrieves customers with last names starting with 'S' . Its ability to handle both partial and fuzzy matches makes it ideal for querying incomplete or uncertain input data, such as partial string information in search applications, trend analysis with keyword detection, or for user-facing queries where precise value inputs are not feasible. Unlike standardized equality checks, LIKE allows for broader matching scope, vital in scenarios like filtering log entries, user searches, or textual data analyses.

The ORDER BY clause is used to sort the result set by one or more columns in ascending (ASC) or descending (DESC) order. It helps organize data to make it meaningful. The LIMIT clause is then used to restrict the number of rows returned by the query to improve query performance or meet specific requirements by returning only a subset of the data. For instance, 'SELECT * FROM PRODUCT ORDER BY P_PRICE DESC LIMIT 3;' sorts products in descending order by price and returns the top three most expensive products . This combination optimizes data retrieval by focusing only on the most relevant or required data output.

The SELECT clause retrieves data from tables, and the use of logical operators like AND, OR, and NOT allows for combining multiple conditions within a WHERE clause to filter records. 'AND' requires all specified conditions to be true, 'OR' requires any of the conditions to be true, and 'NOT' inverts the condition. For example, using 'SELECT * FROM EMP WHERE EMP_TITLE = 'Mr.' AND EMP_AREACODE = '615';' filters employees with the title 'Mr.' in area code '615'. Using 'OR' enables queries like 'SELECT * FROM CUSTOMER WHERE CUS_BALANCE > 300 OR CUS_LNAME = 'Smith';', returning customers with a balance over 300 or last name 'Smith'. 'NOT' is used for negating a condition, like 'SELECT * FROM EMPLOYEE WHERE NOT EMP_YEARS < 10;', which returns employees with 10 or more years of service .

ON DELETE CASCADE is defined in foreign key relationships to automatically delete rows in related tables when a referenced record is removed, maintaining data integrity and reducing orphan data. It simplifies data maintenance across tables that have parent-child relationships. For example, 'CREATE TABLE enrollment FOREIGN KEY(studentid) REFERENCES student(studentid) ON DELETE CASCADE ...' ensures that deleting a student's record will also remove any enrollment records associated with that student . This cascade effect prevents potential data inconsistencies and unnecessary manual clean-up, especially useful in scenarios with multi-layer dependencies, such as transactional databases. However, it requires careful use as it can lead to unintended data loss if not strategically applied or when dependent relationships are inadequately structured, necessitating comprehensive understanding and planning during database design to safeguard against irreversible deletions.

You might also like