Date – 11th Nov 2024 Name – Aayush Mehta

Program-1

Aim  Introduction to SQL

SQL {Structured Query Language}:

SQL (Structured Query Language) is a standard programming language used to manage and
manipulate relational databases. It is widely used for querying, updating, and managing data
in a database, as well as for creating and modifying database structures.

Functionality of SQL:
1. Data Querying: SQL allows for complex queries to retrieve specific data from databases
using the SELECT statement. It supports filtering (WHERE), sorting (ORDER BY),
grouping (GROUP BY), and joining data across multiple tables (JOIN).
2. Data Insertion: SQL allows inserting new records into tables using the INSERT INTO
command.
3. Data Updating: SQL can modify existing records using the UPDATE command.
4. Data Deletion: SQL allows removing records from a table using the DELETE command.
5. Database and Table Management: SQL supports creating, altering, and dropping tables,
views, indexes, and databases, allowing flexible database structure management.
6. Indexing: SQL allows for the creation of indexes to improve the performance of data
retrieval.
7. Constraints and Relationships: SQL can define constraints (such as PRIMARY KEY,
FOREIGN KEY, UNIQUE, CHECK, and NOT NULL) to enforce data integrity and
relationships between tables.

Characteristics of SQL:
1. Declarative Language: SQL allows users to specify what they want to do with the data
without defining how to accomplish it. This simplifies database interaction by focusing on
"what" rather than "how."
2. Data Definition Language (DDL): SQL includes commands to define database structures,
such as creating, altering, and dropping tables or databases (CREATE, ALTER, DROP,
TRUNCATE).
Commands:
CREATE: Used to create new database objects such as tables, indexes, or views.

1
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

ALTER: Used to modify existing database objects like tables.

DROP: Deletes existing database objects like tables or databases.

TRUNCATE: Removes all records from a table but keeps the structure.

3. Data Manipulation Language (DML): SQL is used to manipulate data within tables,
allowing users to insert, update, delete, or query records (SELECT, INSERT, UPDATE,
DELETE).
Commands:
SELECT: Retrieves data from the database.

2
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

INSERT: Inserts new data into a table.

UPDATE: Modifies existing data in a table.

DELETE: Removes records from a table.

4. Data Control Language (DCL): SQL includes commands to control access to data, such
as granting or revoking permissions (GRANT, REVOKE).
Commands:
GRANT: Provides access privileges to the users.

REVOKE: Removes access privileges from users.

5. Transaction Control Language (TCL): SQL supports managing transactions within

databases, including the ability to commit or roll back operations (COMMIT, ROLLBACK,
SAVEPOINT).
3
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Commands:
COMMIT: Saves all changes made in the transaction to the database.

ROLLBACK: Reverts changes made in the transaction if an error occurs.

SAVEPOINT: Sets a point within a transaction to which a rollback can be performed.

SET TRANSACTION: Defines the properties of a transaction, such as isolation level.

6. Standardized Language: SQL is a standardized language with support across different

relational database management systems (RDBMS), such as MySQL, PostgreSQL, Oracle,
and SQL Server.

Program-2

Aim  To study Basic SQL commands (create database, create table, use, drop, insert,
delete, show) and execute the queries using these commands.

Pre-Questions:

4
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

1) What is RDBMS? Give some examples of RDBMS?

Ans- RDBMS stands for Relational Database Management System. It is a database
management system based on a relational model, where data is stored in tables with
rows and columns. The tables in an RDBMS can be related to each other using keys
like primary keys and foreign keys. RDBMSs provide tools to perform various
operations like querying, updating, and managing the data in a structured way using
SQL (Structured Query Language).

Examples of RDBMS:
- MySQL
- PostgreSQL
- Oracle Database
- Microsoft SQL Server
- SQLite

2) What are tables and fields in SQL?

Ans- A table in SQL is a collection of related data organized in rows and columns.
Each table represents a specific entity, and the data within it is stored in a structured
format.
Fields (also called columns) represent the attributes of the data, and each field stores a
specific type of information for each record. For example, in a table called Customers,
the fields might be CustomerID, Name, Address, and PhoneNumber.

Example of a table:
CustomerID Name Address PhoneNumber
1 Aayush Karnal 1234567890
2 Payal Panipat 1234567891
In this example, CustomerID, Name, Address, and PhoneNumber are fields (columns), while
the data for each customer forms the rows (records).

3) Does SQL support programming language features?

Ans- SQL is primarily a query language used for managing and retrieving data from
relational databases. It does not inherently support traditional programming language
features like loops, conditionals, or variable declarations in its basic form. However,
most RDBMSs extend SQL with procedural extensions such as PL/SQL (in Oracle),
T-SQL (in Microsoft SQL Server), or PL/pgSQL (in PostgreSQL), which allow for
programming constructs like loops, conditionals, and variable handling. These are
used to create stored procedures, functions, and triggers, enabling SQL to perform
more complex tasks.

5
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

1. Create Database: To create database.

2. Use Database: To switch context to database.

3. Show Database: To list all databases.

4. Create Table: To create table in database.

5. Show Tables: To list all the tables in selected database.

6
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

6. Insert Data: To insert data inside database.

7. Select Data: To select data required in database.

8. Delete Records: To delete specific or all records from a table.

7
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

9. Drop Table: To completely delete the table.

8
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

10. Drop Database: To completely delete the database.

Post-Questions:
1) What is the difference between Char and Varchar 2 in data type in SQL?
Ans- CHAR: It is a fixed-length data type. If you define a column as CHAR(10) and
store a 5-character word, SQL will pad the remaining space with spaces until the
length is 10 characters.
VARCHAR2: It is a variable-length data type. When you define a column as
VARCHAR2(10) and store a 5-character word, only 5 characters are stored without
padding the rest.

2) What is unique key and primary key?

Ans- Primary Key: It uniquely identifies each record in a table. A table can have
only one primary key, and it cannot contain NULL values.
Unique Key: It also ensures that all values in a column or set of columns are unique.
However, unlike the primary key, a table can have multiple unique keys, and a unique
key can contain NULL values.

3) What is schema?
Ans- A schema in SQL is a logical collection of database objects, such as tables,
views, procedures, etc., owned by a database user. It helps organize and manage data
0structures within the database.

4) What is SQL comment?

Ans- SQL comments are used to add explanations or notes within the SQL code,
making it more readable. They are ignored during SQL execution. There are two types
of comments in SQL:
- Single-line comment: -- This is a comment
- Multi-line comment: /* This is a multi-line comment */

Program-3

Aim  To study the viewing commands (select, update) and execute the queries using these
commands.

Pre-Questions:
1) What is Clause?
Ans- A clause in SQL is a part of a query that specifies a condition or modifies the
behaviour of a statement. Clauses typically work in conjunction with SQL commands
such as SELECT, INSERT, UPDATE, and DELETE. Examples of clauses include:
9
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

WHERE: Used to filter records based on conditions.

ORDER BY: Used to sort the result set in ascending or descending order.

GROUP BY: Used to group rows sharing a property so that aggregate functions can
be applied.

2) What is Sub-Query?
Ans- A sub-query (also known as an inner query or nested query) is a query within
another query. A sub-query is usually enclosed within parentheses and is used to
retrieve data that will be used by the main query. Sub-queries are commonly used
with SELECT, INSERT, UPDATE, or DELETE statements to perform more complex
operations.

3)
What is an Alias?
Ans- An alias in SQL is a temporary name assigned to a table or column. It makes the
SQL query more readable and can be used to shorten table or column names in the
output or in complex queries.

Column Alias: Assigns a temporary name to a column in the result set.

Table Alias: Assigns a temporary name to a table, often used in joins to make the
query more concise.

Make a table of Employees:

Columns: emp_no, f_name, l_name, city, salary, job_id, doh (datetime)

Insertion of data:

10
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query for emp_no and f_name where salary > 5000:

Write a SQL query for emp_no and doh for all hiring:

Write a SQL query to update salary to 10000 whose salary < 5000:

11
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query to update l_name and city of employee whose job_id is AB-123:

Post-Questions:
1) Write an SQL query to fetch current date and time from the system?
Ans- In MySQL, you can fetch the current date and time using the NOW() function:

In SQL Server, you would use GETDATE():

In PostgreSQL, you would use CURRENT_TIMESTAMP:

12
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

2) Explain difference between delete and truncate command?

Ans-
DELETE:

The DELETE command is used to remove specific rows from a table based on a
condition.

It can be used with a WHERE clause to specify which rows to delete.

It is a DML (Data Manipulation Language) command.

It removes rows one at a time, and the action can be rolled back (if within a
transaction).

Example:

TRUNCATE:

The TRUNCATE command is used to remove all rows from a table quickly.

It cannot be used with a WHERE clause; it always deletes all rows.

It is a DDL (Data Definition Language) command.

The operation cannot be rolled back (in most databases), and it also resets any auto-
increment counters.

Example:

Key Differences:

DELETE is slower and can be used with conditions; it can be rolled back.

TRUNCATE is faster and removes all rows without conditions; it cannot be rolled
back in most cases.

3) Explain the difference between SQL and No SQL database?

Ans-
SQL Databases:

13
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Structured: SQL databases are relational and store data in tables with predefined
schemas (structured data).

ACID Compliance: They ensure properties like Atomicity, Consistency, Isolation,

and Durability, which guarantee reliable transactions.

Query Language: SQL databases use Structured Query Language (SQL) for defining
and manipulating data.

Examples: MySQL, PostgreSQL, Oracle, SQL Server.

NoSQL Databases:

Unstructured or Semi-structured: NoSQL databases are non-relational and can

store data in various formats like documents (JSON), key-value pairs, graphs, or
columns. No strict schema is required.

Flexible Schema: They provide more flexibility with the structure of data, making
them suitable for unstructured or semi-structured data.

Eventual Consistency: Instead of strict ACID compliance, some NoSQL databases

follow BASE properties (Basically Available, Soft state, Eventual consistency).

Examples: MongoDB, Cassandra, Redis, Couchbase.

Program-4

Aim  To study the commands to modify the structure of table (alter, delete, drop, add,
modify) and execute the queries using these commands.

Pre-Questions:
1)What is a View and Why we use it?
Ans- A view in SQL is a virtual table that is based on the result of a SELECT query. It doesn't
store data physically but provides a way to look at the data in one or more tables. Views are
used to simplify complex queries, provide security (by restricting access to certain data), and
present data in a specific format.
Usage of View:

14
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Simplifies complex queries by abstracting them.

Provides a level of security by restricting access to specific rows/columns.
Can present aggregated or computed data without storing it physically.
Allows hiding of the actual table structure from the end user.

2) How to insert many rows in a table (write general syntax)?

Ans- To insert multiple rows in a table, you use the INSERT INTO statement and provide
multiple sets of values. The syntax for inserting multiple rows is as follows:

3) What is Foreign Key?

Ans- A foreign key is a field (or collection of fields) in one table that uniquely identifies a
row in another table. It establishes a relationship between two tables. The table containing the
foreign key is called the child table, and the table that the foreign key references is called the
parent table.
The foreign key ensures referential integrity, meaning the values in the child table correspond
to valid values in the parent table.

Make a table of Employees:

Columns: emp_id, name, country, doh, salary, salary, dep_id, email

15
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Insertion of data:

16
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query to rename column country to city:

Write a SQL query to add a column state:

Write a SQL query to add a column m_id with default value a-101:

Write a SQL query to change data type of doh (from date to datetime):

Write a SQL query to drop column dep_id:

17
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Post-Questions:
1) What is constraint in SQL? Name few of them.
Ans- A constraint in SQL is a rule applied to a column or a set of columns in a table to
enforce data integrity, ensuring the accuracy and reliability of the data in the database.
Constraints help ensure that the values in a column comply with certain conditions.
Common types of SQL constraints:
PRIMARY KEY: Ensures that a column or a combination of columns has unique values and
cannot be NULL.
FOREIGN KEY: Ensures referential integrity between two tables by making sure a
column’s values correspond to valid values in another table.
UNIQUE: Ensures that all values in a column are unique (except NULL).
NOT NULL: Ensures that a column cannot have NULL values.
CHECK: Ensures that all values in a column satisfy a specific condition.
DEFAULT: Sets a default value for a column when no value is provided during an insert
operation.

2) What is difference between WHERE and HAVING clause?

Ans- The WHERE and HAVING clauses are both used to filter rows in SQL, but they serve
different purposes and are applied at different stages in query execution.
WHERE:
Filters rows before any grouping or aggregation takes place.
Can be used with SELECT, UPDATE, DELETE statements.
18
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Cannot be used with aggregate functions like SUM(), COUNT(), AVG(), etc

HAVING:
Filters rows after grouping or aggregation has taken place.
Used in conjunction with the GROUP BY clause.
Can be used with aggregate functions.

3) What are different types of SQL relationships?

Ans- In SQL databases, relationships define how tables are linked or related to each other.
The relationships are established using primary keys and foreign keys. The common types of
relationships are:
1. One-to-One (1:1):
Each row in Table A is related to exactly one row in Table B, and vice versa.
Example: A table of users and a table of user profiles, where each user has only one profile.

2. One-to-Many (1:N):
Each row in Table A is related to one or more rows in Table B, but each row in Table B is
related to only one row in Table A.
Example: A customer can have many orders, but an order is placed by only one customer.

19
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

3. Many-to-Many (N:N):
Each row in Table A can be related to multiple rows in Table B, and each row in Table B can
be related to multiple rows in Table A. This is often implemented using a junction table (a
table that connects the two tables).
Example: A student can enrol in multiple courses, and a course can have multiple students.

20
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Program-5

Aim  To study the commands that involve compound conditions (and, or, in, not in,
between, not between, like, not like) and execute the queries using these commands.

Pre-Questions:
1) Difference between SQL, MySQL and SQL Server?
Ans-
SQL (Structured Query MySQL: SQL Server (Microsoft
Language): SQL Server):
MySQL is an open-source
SQL is a standard relational database SQL Server is a relational
programming language management system database management
used to manage and (RDBMS) that uses SQL system developed by
manipulate relational as its language for Microsoft. Like MySQL,
databases. It is a language querying data. SQL Server also uses
that provides commands SQL to manage and
for querying, updating, MySQL is widely used manipulate data.
and managing data in for web applications and
databases. supports SQL to manage It is often used in
data. enterprise environments
SQL itself is not a with .NET applications
database, but rather the It was developed by and integrates well with
language used to interact Oracle Corporation and is Microsoft services.
with databases. commonly used with PHP
and Linux in the LAMP It supports features like
Common SQL stack. advanced analytics,
commands: SELECT, integration services, and
INSERT, UPDATE, business intelligence.
DELETE, CREATE,
DROP, ALTER, etc.

2) What is an index in SQL?

Ans- An index in SQL is a database object that improves the speed of data retrieval
operations on a table by providing a fast lookup mechanism. Indexes are created on
columns in a table to enhance the performance of SELECT queries.
Indexes work similarly to an index in a book. Instead of scanning the entire table, the
database can quickly locate the row using the index.
However, while indexes speed up read operations, they can slow down write
operations (such as INSERT, UPDATE, DELETE) since the index must be updated
when the data in the table changes.

21
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

3) What is default constraint?

Ans- A default constraint in SQL is used to provide a default value for a column when
no explicit value is specified during an insert operation. This ensures that the column
has a value even if it is not provided by the user.

Make a table of Employees:

Columns: c_id, name, c_name, city, grade, s_id

Insertion of data:

Write a SQL query about customer with their c_id, c_name, city where grade value is
100 or 300.

22
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query for c_id, c_name, s_id of the customer who lives in New York city
and have grade is above 100.

Write a SQL query for list of customers from California and whose grade is either 200
or 400.

Write a SQL query for table name customer where s_id is 1001, 1005, 1006, 1010.

Write a SQL query for table name customer where s_id is not 1001, 1005, 1006, 1010.

Write a SQL query for c_name, city, s_id where c_id is between 3005 to 3009.

23
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query for c_name, city, s_id where c_id is not between 3005 to 3009.

Write a SQL query for customer table where c_name starts from ‘d’.

Write a SQL query for customer table where c_name ends with ‘h’.

Write a SQL query for customer who recites in city starts with ‘l’ followed by one wild
card character and then ‘nd’ and then two wild card characters.

24
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Post-Questions:
1) Difference between Primary Key and Unique Constraint?
Ans-
Primary Key: Unique Constraint:

A primary key uniquely identifies each A unique constraint also ensures that all
row in a table. values in a column or a group of
columns are distinct (unique).
It cannot contain NULL values.
However, unlike the primary key, a
A table can only have one primary key, column with a unique constraint can
and it can consist of single or multiple contain NULL values, but there can only
columns (composite key). be one NULL value per column.

The primary key automatically creates a A table can have multiple unique
unique index for faster retrieval of data. constraints.

The primary key uniquely identifies The unique constraint allows NULL
rows and does not allow NULL values. values but ensures that all non-NULL
values are unique.

2) Are null values same as 0 and blank space?

Ans- No, NULL values are not the same as 0 or blank spaces.

NULL: Represents the absence of a value or an unknown value. It indicates that no

data has been entered into the field.

0: Is a numeric value representing zero. It is still considered a valid value, unlike

NULL.

Blank Space (' '): Is a valid character value, but it indicates an empty string or a
space in a text field.

In SQL:
25
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Comparisons with NULL return UNKNOWN. For example, NULL = NULL is not
true, as NULL represents an unknown value.

To check for NULL, you must use IS NULL or IS NOT NULL.

3) Difference between SQL and PL/SQL?

Ans-
SQL (Structured Query Language): PL/SQL (Procedural Language/SQL):

SQL is a declarative language used for PL/SQL is an extension of SQL designed

querying, updating, and managing data by Oracle. It adds procedural
in relational databases. capabilities, allowing you to write
programs with logic, loops, conditions,
It is primarily used to perform CRUD and more.
operations (Create, Read, Update,
Delete) on data. PL/SQL supports features like variables,
loops, conditions, and exceptions,
SQL is a non-procedural language, enabling you to create stored procedures,
meaning you state what you want to do, functions, triggers, and packages.
not how to do it.
PL/SQL is procedural, meaning you
SQL is used for simple data define a sequence of steps to perform
manipulation and retrieval. specific tasks.

SQL executes one query at a time. PL/SQL is used for complex

programmatic logic.

PL/SQL allows blocks of code with

multiple queries and logic.

Program-6

26
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Aim  To study the aggregate functions (sum, count, max, min, average) and execute the
queries using these commands.

Pre-Questions:
1) Define Data Integrity?
Ans- Data Integrity refers to the accuracy, consistency, and reliability of data
throughout its lifecycle. It ensures that the data remains unaltered and reliable when
stored, processed, or retrieved. Data integrity can be enforced by database constraints
like primary keys, foreign keys, and unique keys.

2) Define Alias Command?

Ans- An Alias Command is used in SQL to give a temporary name to a table or a
column in a query. It helps make query results more readable and understandable. For
example, the alias for a column can make it easier to interpret the output.

3) Differentiate between Inner Joint and Outer Joint?

Ans- Inner Join: Returns only the rows where there is a match in both joined tables.
Outer Join: Returns all rows from one table and the matched rows from the other
table. If there is no match, NULL values will be returned for columns from the non-
matching table. It can be LEFT, RIGHT, or FULL outer join.

Make a table of Employees:

Columns: ord_no, prch_amt, ord_date, c_id, s_id

Insertion of data:

Write a SQL query to calculate total purchasing amount of all the orders.

27
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query to calculate average purchasing amount of all the orders.

Write a SQL query to count the number of unique salesperson.

Write a SQL query to find the maximum purchase amount.

Write a SQL query to find the minimum purchase amount.

28
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query to count the orders generated on ‘2023-12-03’.

Write a SQL query to find the highest purchasing amount ordered by each customer,
return customer id and maximum purchasing amount.

Write a SQL query to find the highest purchasing amount on ‘2024-06-08’ sold by each
salesperson, return salesman id and maximum purchasing amount.

Write a SQL query to find the highest purchasing amount generated by each
salesperson filter the rows for salesperson id in the range 2000 to 2400.
29
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Post-Questions:
1) Differentiate between a View and Table in SQL?
Ans- Table: A table is a structured collection of data that is stored physically in a
database.

View: A view is a virtual table based on the result of an SQL query. It does not store
data physically but dynamically retrieves data from underlying tables whenever
accessed.

2) Can a View be created based on another View?

Ans- Yes, a view can be created based on another view. This is known as a nested
view or view of views, where the result of one view can be further used in another
view's definition.

3) Why Group Functions are required in SQL?

Ans- Group Functions (such as SUM, COUNT, AVG, MIN, MAX) are essential for
performing calculations on a set of rows. These functions allow us to aggregate data
and get meaningful insights, such as total sales, average scores, or maximum salaries,
across rows in a table.

30
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Program-7

Aim  To study the grouping commands (group by, order by) and execute the queries using
these commands.

Pre-Questions:
1. What is an Index?
Ans- An index is a database object that improves the speed of data retrieval
operations on a database table. It functions like a lookup table, allowing the database
engine to find rows more quickly without scanning the entire table.

2. What are different types of Indexes?

Ans- Unique Index: Ensures that all values in the indexed column are unique.
Clustered Index: Determines the physical order of data in the table. Each table can
have only one clustered index.
Non-Clustered Index: A separate structure from the table that contains pointers to
the table data. A table can have multiple non-clustered indexes.
Full-Text Index: Used for full-text searches, allowing for searching within text
columns.
Bitmap Index: Efficient for columns with a low number of distinct values, often used
in data warehousing.

3. What is Trigger?
Ans- A trigger is a special type of stored procedure that automatically runs in
response to certain events on a particular table or view. Common events include
INSERT, UPDATE, and DELETE operations. Triggers help enforce business rules
and maintain data integrity.

31
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Make a table box_office:

Columns: id, movie_name, city, country, gross(float)

Insertion of Data:

Write a SQL query to get movie name from table group by and order by movie name in
ascending order:

Write a SQL query to get movie name, sum of gross from table group by movie name
order by sum of gross in descending order:

32
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write a SQL query to get sum of gross, movie name, city from table group by movie
name and city order by city in ascending order:

Write a SQL query to get city from table group by city order by sum of gross:

Write a SQL query to get city, country, average of gross from table group by city order
by country:

33
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Post-Questions:
1. What is primary use of normalization?
Ans- The primary use of normalization is to reduce data redundancy and improve data
integrity in a relational database. By organizing tables and their relationships properly,
normalization helps ensure that data is stored logically and can be easily updated
without inconsistencies.

2. How do you optimize SQL Queries?

Ans- Use Indexes: Implement appropriate indexes on frequently queried columns.
Avoid SELECT: Specify only the columns you need to reduce data transfer.
Use Joins Wisely: Opt for INNER JOINs instead of OUTER JOINs when possible.
Filter Early: Use WHERE clauses to limit the result set as early as possible.
Analyze Query Execution Plans: Understand how queries are executed and look for
bottlenecks.
Batch Operations: Use batch processing for large data changes instead of single
transactions.

3. What is database warehouse?

Ans- A data warehouse is a centralized repository designed for storing and analyzing
large volumes of structured and unstructured data from various sources. It supports
business intelligence activities, including data analysis, reporting, and querying, and is
optimized for read-heavy operations rather than transaction processing. Data is often
organized in a way that facilitates complex queries and analysis, making it a key
component of decision support systems.

34
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Aim  To understand and implement key database concepts, including:

i. Aliasing and Renaming
ii. Data Constraints

Program-8

Pre-Questions:
1. What is denormalization?
Ans- Denormalization is a database optimization technique where redundant data is
added to improve query performance. It involves combining data from multiple
normalized tables into a single table, reducing the number of joins needed for
complex queries. This approach improves read performance but can increase
redundancy and storage requirements.

2. What do you mean by aggregate and scalar function?

Ans- Aggregate Functions: Perform calculations on a set of values and return a
single value. Examples include sum(), avg(), count(), max(), and min().
Scalar Functions: Operate on a single value and return a single value. Examples
include ucase(), lcase(), len(), and round().

3. How do you prevent SQL injection?

Ans- SQL Injection can be prevented by:
a. Using prepared statements with parameterized queries.
b. Implementing stored procedures.
c. Validating and sanitizing user inputs.
d. Limiting database permissions.
e. Using ORM (Object-Relational Mapping) tools.
f. Employing web application firewalls (WAFs).

Make a table employees:

Employee id, first name, last name, salary, address, city, country

Insertion of data:

35
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write an SQL query to give temporary name to column employee_id as emp_id and to
get first name:

Write an SQL Query to get first and last name of all the employees as f_name and
l_name whose salary is above 200000:

36
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write an SQL query to get employee_id, salary and complete address of all the
employees:

Write an SQL query to rename the table name from employee to emp_one:

Post-Questions:
1. Define database replication?
Ans- Database replication involves copying and maintaining database objects like
tables or transactions across multiple databases. This ensures data consistency and
availability, often for fault tolerance or load balancing. Common replication types
include Master-Slave Replication, Master-Master Replication, and Log-Based
Replication.

2. How can you handle missing values(NULL) ?

Ans- Missing values in a database can be handled by:
37
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

a. Substituting default values.

b. Using COALESCE() or IFNULL() to replace NULL values in queries.
c. Applying data imputation techniques in analytics (e.g., mean, median).
d. Ensuring NOT NULL constraints where applicable.
e. Skipping or excluding rows with NULL values during analysis

3. Explain the difference between union and union all?

Ans- UNION: Combines the result sets of two queries and removes duplicate rows
from the final result.
UNION ALL: Combines the result sets of two queries but does not remove
duplicates, making it faster than UNION.
Key Difference: UNION ensures unique rows, while UNION ALL includes all rows,
even duplicates.

Program-9

Aim  To study and implement JOIN operations(INNER, LEFT, RIGHT, CROSS)in

MySQL.

Pre-questions:
1. What is the purpose of using alias in joins?
Ans- Aliases are used in SQL joins to give temporary names to tables or columns,
making the query more readable and reducing ambiguity, especially when dealing
with self-joins or queries involving multiple tables with similar column names.

2. What the difference between on and where clause?

Ans- ON Clause: Used to specify the join condition between two tables in JOIN
operations. It is evaluated before filtering rows.

WHERE Clause: Used to filter rows after the join has been performed. It applies
additional conditions to the result set.

Make 4 tables:
Author: id, name, birth year, death year
Book: id, author id, title, publish year, publish house
Adaption: book id, type, title, rating, release year
Book Review: book id, review, author

Insertion of data:

38
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

39
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

40
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write an SQL query to select author.name, book.title, book.publish_year from author

inner join book on author.id = book.id;

Write an SQL query to select book.title, book.publish_year, adaption.title,

adaption.release_year from book inner join adaption on book.id = adaption.book_id;

Write an SQL query to select book.title, adaption.title, adaption.release_year from

adaption left join book on adaption.book_id = book.id;

41
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Write an SQL query to select book.title, book_review.review, book_review.author from

book right join book_review on book.id = book_review. book_id;

Write an SQL query to select book.title, author.name from book left join author on
book.author_id = author.id union select book.title, author.name from book right join
author on book.author_id = author.id;

Post-questions:
1. What are acid properties?
42
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Ans- ACID properties ensure reliable database transactions:

i. Atomicity: Ensures a transaction is fully completed or not executed at
all.
ii. Consistency: Guarantees that a transaction brings the database from
one valid state to another.
iii. Isolation: Ensures that concurrent transactions do not interfere with
each other.
iv. Durability: Ensures that once a transaction is committed, changes are
permanent even in the case of system failures.

2. What is the need for merge statement?

Ans- The MERGE statement allows the combination of INSERT, UPDATE, and
DELETE operations in a single statement. It is particularly useful for implementing
UPSERT logic (update existing records or insert new ones based on a condition).

3. State the diff between cross join and natural join?

Ans- CROSS JOIN: Produces the Cartesian product of two tables, combining every
row of the first table with every row of the second table.

NATURAL JOIN: Combines tables based on columns with the same names and
compatible data types, automatically performing an equi-join on those columns.

Program-10

Aim  To study the use of group and having clause.

Pre-Questions:
1. Having clause works like where clause if it is not work with group by clause.
True or False?
Ans- True

2. A select statement can’t be contained both where and having clause.

True or False?
Ans- False

43
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Make a table orders:

Columns: order number, purchase amount, date, customer id, sales id

Insertion of data:

Write an SQL query to select sales_id, max(purchase_amount) as max_amt from orders

group by sales_id having sales_id between 101 and 103;

Write an SQL query to select sales_id, max(purchase_amount) as max_amt from orders

group by sales_id having max(date) = "2024-11-15";
44
Reg. No. – 11023210098
Section – “B”
Date – 11th Nov 2024 Name – Aayush Mehta

Post-Questions:
1. What happens to NULL when you use Group by?
Ans- When using GROUP BY, NULL values are treated as a distinct group. This
means that all rows with NULL in the grouped column will be grouped together.

2. State the difference between Correlated Subquery and Nested Subquery?

Ans- Correlated Subquery: A subquery that uses values from the outer query. It is
executed repeatedly, once for each row considered by the outer query.

Nested Subquery: A subquery that is independent of the outer query and is executed
only once.

45
Reg. No. – 11023210098
Section – “B”