MySQL

IN Operator (filtering data): The operator is used to filter data and allow to determine if the value matches anyone of
the list.
NOT IN Operator (filtering data): Is used to filter data and allow to determine if the value doesn’t matches anyone of the
list.

Q. Find faculty name who are from department 1 or 3?

Ans. SELECT fname from faculty where depID IN (1,3);

Q. Find faculty details having either of the following qualification B. Tech and PHD?
Ans. SELECT * FROM faculty where qualification in (‘B. Tech’, ’PHD’);

Q. Find student details who are from IT and CSE branch?

Ans. SELECT 8 FROM students where branch NOT IN (‘IT’, ‘CSE’);

Between Operator (filtering data): Is used to filter data and allow to specify range of values to test.
Not Between Operator: It is opposite of between operator.

Q. Find student details whose marks are between 60 to 90?

Ans. SELECT * FROM students where marks between 60 AND 90.

Q. Find student details whose marks are not between 70 and 90?
Ans. SELECT * FROM student where marks not between 70 AND 90

Limit: Is used to contain the number of rows written by the SELECT statement.
IS NULL: Is used to check whether a value is null or not.
IS NOT NULL: It is the opposite of NULL.

Q. Find first four records from faculty table?

Ans. SELECT * FROM faculty limit 0,4;

Q. Find second and third from faculty table?

Ans. SELECT * FROM faculty limit 1,2;

Q. Find student name with second highest marks?

Ans. SELECT * FROM students order by desc marks limit 1,1;

Q. Find the second highest salary of the employee?

Ans. SELECT * FROM employee order by desc limit 1,1;

Q. Find student name who is not allotted to any branch?

Ans. SELECT * sname FROM student where branch IS NULL.

Q. Find student name who is are allocated to at least one branch?

Ans. SELECT sname FROM student where branch IS NOT NULL.

Order by (sorting data): Clause is used to sort the result of the SELECT statement.
Note: By default, sorting happens in ascending order.
Q. Find student details and sort a result by student name in ascending order?
Ans. SELECT * FROM students order by sname asc;

Q. Find student details and sort a result by marks descending order?

Ans. SELECT * FROM students order by marks desc;

Like Operator: It is used to select rules or data and pattern. Following to wild word characters are used with like
operator.
%: It matches any string of zero or more character.
_:- : It matches exactly column FROM table where condition like “pattern”;

Q. Find faculty details whose fname start ‘A%’; ?

Ans. SELECT * FROM faculty where fname like ‘A%’;

Q. Find faculty details whose name ends with letter N?

Ans. SELECT * FROM faculty where fname like ‘N%’;

Q. Find student marks contains letter A as a second letter?

Ans. SELECT sname marks FROM students where sname like ‘_A%’;

Q. Find student marks whose name start with letter P and contains only 5 letters in a name?
Ans. SELECT sname, FROM students where sname like ‘P____’;

Q. Find faculty details whose name not ends with letter N?

Ans. SELECT * FROM faculty where fname not like ‘%N’;

Q. Find student details whose name doesn’t contain second letter A?

Ans. SELECT * FROM student where sname not like ‘_A%’;

JOINS
Cross Join: It returns the cartizan product of rows from the join table.

Q. Perform cross join between faculty and department table?

Ans. SELECT * FROM faculty cross join department cross join students;

Q. Perform cross join between faculty and department table and should contain department no.1 record?
Ans. SELECT * FROM faculty cross join department where department. DepID=1;

Inner Join: It matches rows of one table with another table.

Q. Find department name of each faculty?
Ans. SELECT fname, d. dname FROM faculty F inner join department d ON f. deptid= d. deptid;

Q. Find department name of faculty working in department 2?

Ans. SELECT fname, d. dname from faculty F inner join department d ON f. deptid= d. deptid where d. deptid=2;

Left Join: Left join results in all the matching rows from 2 tables and the rows that are not matching from left table.
Q. Perform left join between faculty and department?
Ans. SELECT f. fname, d. dname FROM faculty F left join department d ON F. deptid= d. deptid;

Right Join: Right join results in all the matching rows from 2 tables and the rows that are not match from right table.

Q. Perform right join between faculty and department?

Ans. SELECT f. fname, d. dname from faculty F right join department d ON f. deptid= d. deptid;

Q What is the difference between DELETE, TRUNCATE, and DROP?

Ans. DELETE: Removes rows one by one, can use WHERE clause, can be rolled back
TRUNCATE: Removes all rows quickly, resets auto-increment, cannot be rolled back
DROP: Removes entire table structure and data

Sub Query (Nested Query): A nested query, also known as a subquery, is a SQL query written inside another query to
provide intermediate results used by the main query, often in WHERE, FROM, or SELECT
clauses.

QS. Find the name of faculty working in Applied Science department?

Ans. SELECT fname FROM faculty where depid IN (SELECT depid FROM department where dname= ‘Applied Science’)

Q. What is MySQL?
Ans. MySQL is a popular open-source relational database management system (RDBMS). It is used to store, retrieve,
manage, and manipulate data in structured formats.

Q. What is SQL?
Ans. SQL stands for Structured Query Language. It is a standard programming language specifically designed for
managing and manipulating relational databases.

Q. What are datatypes, tables, and field?

Ans. Data types define the kind of data a column can store—like numbers, text, dates, etc.
A table is a collection of related data organized in rows and columns. Each column has a specific name and data
type, and each row is a single record in that table.
A field is a column in the table. It represents a specific attribute or property of the data.

Q. What are different type of datatypes in MySQL?

Ans. Int, varchar, date etc.

Q. What is primary key, foreign key, and unique key?

Ans. A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table and cannot be
NULL or duplicated.
A foreign key is a column or group of columns that establishes a link between data in two tables. It references the
primary key of another table, enforcing referential integrity.

A Unique Key in MySQL ensures that all values in a column (or group of columns) are distinct, but unlike a primary
key, it can contain NULL values.

Q. What are indexes and why they are important?

Ans. It is used to quickly identify rows with specific column values. It is important to speed up the data retrieval.
Q. What are constrains and their types?
Ans. Constrains are rules or condition that define the structure and integrity of data.
Types- Primary key, foreign key, unique, not null, check and, default.

Q. Difference between WHERE and HAVING?

Ans. WHERE clause filter rows before grouping while HAVING clause filter groups after grouping for condition based on
aggregate function.

Q. What are aggregate functions?

Ans. COUNT, SUM, AVG, MAX, MIN.

Q. Use of GROUP BY and ORDER BY?

Ans. Group rows that have the same values into summary rows. Like find the no of customer in each country:
SELECT column name(s) from table name WHERE condition GROUP BY column name(s) ORDER BY column
name(s)
ORDER BY clause is used to sort result ASC and DSC order.

Q. How do you use LIMIT?

Ans. SELECR * FROM product LIMIT 5;
It is used to contain the no of records returned by a query.

Q. Different types of statement (commands) in MySQL?

Ans. DML (Data Manipulation Language): Insert, update and, delete. We cannot put constrain.
DDL (Data Definition Language): Create, alter and, drop. We can put constrain.
DCL (Data Control Language): GRANT and REVOKE.
TCL (Transaction Control Language): BEGIN, COMMIT, Rollback, save point, set transaction.

Q. What is normalization?
Ans. Normalization in MySQL is the process of organizing data to reduce redundancy and improve large scale data to
small case data.

Q. Union and Union all operator?

Ans. UNION combines results from two or more SELECT queries and removes duplicates.
UNION ALL combines results from multiple SELECT queries including duplicates.
Example
SELECT name FROM employees
UNION/ UNION ALL
SELECT name FROM managers;

Q. Second highest salary?

Ans. SELECT MAX (salary) FROM employee
SELECT MAX (Salary) FROM employee WHERE salary <(SELECT MAX (salary) FROM employee);

Q. What are views in MySQL?

Ans. A view in MySQL is a virtual table created from a SQL query that simplifies data access and enhances security
without storing data physically.
Q. Convert text into date form?
Ans. SELECT STR-TO- DATE (’20-07-2025, ‘%D-%M-%Y’’); 20-07-2025

Q. What are triggers in MySQL?

Ans. Triggers are predefined actions that are automatically executed in response to specific events (INSERT, UPDATE,
DELETE) on a table.

Q. DBMS and RDBMS?

Ans. DBMS (Database Management System) is software that stores, manages, and retrieves data.
RDBMS (Relational Database Management System) is a type of DBMS that organizes data into related tables using
rows and columns — MySQL is an example of an RDBMS.

Q. ACID properties?
Ans. ACID stands for Atomicity, Consistency, Isolation, and Durability, and these are the key properties that ensure
reliable transaction processing in MySQL:
1. Atomicity – A transaction is all-or-nothing; either all operations succeed, or none do.
2. Consistency – A transaction brings the database from one valid state to another, maintaining data integrity.
3. Isolation – Concurrent transactions are executed independently, without interference.
4. Durability – Once a transaction is committed, its changes are permanently stored, even in case of a crash.
MySQL supports ACID compliance when using Inno DB as the storage engine.

Q. CHAR and VARCHAR?

Ans. CHAR is a fixed-length data type; it always stores the same number of characters by padding with spaces if needed.
VARCHAR is a variable-length data type; it only stores the actual characters entered, along with an extra byte (or
two) to track length.

Q. What is case in MySQL?

Ans. CASE statement is used to implement conditional logic in SQL queries, similar to an IF-ELSE structure in
programming. It allows you to return different values based on different conditions.

Q. Stored Procedure?
Ans. A stored procedure in MySQL is a precompiled set of SQL statements stored in the database that can be executed
repeatedly to perform specific tasks.

Q. Optimize subqueries?
Ans. To optimize subqueries in MySQL, use JOINs instead of subqueries, avoid correlated subqueries, and ensure proper
indexing on filtered columns.

Q. What is transaction?
Ans. A transaction in MySQL is a sequence of one or more SQL operations executed as a single unit, ensuring ACID
properties—Atomicity, Consistency, Isolation, and Durability—to maintain data integrity.