DBMS SQL MSBTE III Sem

Unit 3 - SQL

Q1. List any four data types in SQL - 2 Marks

1.    INT: Used to store integer (whole number) values.

o   Example: INT, SMALLINT, BIGINT

2.    VARCHAR(size): Used to store variable-length strings (text) up to a specified size.

o   Example: VARCHAR(255) stores up to 255 characters.

3.    DATE: Used to store date values (year, month, day).

o   Example: DATE stores values in the format YYYY-MM-DD.

4.    DECIMAL(p, s): Used to store exact numeric values with a fixed number of decimal places.

o   Example: DECIMAL(10, 2) stores numbers with up to 10 digits, 2 of which can be after the decimal point.

These are just a few examples, as SQL supports a wide range of data types for different purposes.

 

Q2. State the use of avg function with example - 2 Marks

The AVG() function in SQL is used to calculate the average value of a numeric column. It ignores NULL values when performing the calculation

Syntax : SELECT AVG(column_name) FROM table_name;

Example : SELECT AVG(Salary) AS AverageSalary FROM Employees;

---------------------------------------------------------------------------------------------

Sample Answer 2

In SQL, the AVG() function is used to calculate the average value of a numeric column. It is often used in aggregation to get the mean of a set of values, like the average salary, age, or score in a table. Here’s how it works:

Syntax :

SELECT AVG(column_name) FROM table_name WHERE condition;

SELECT AVG(Salary) AS AverageSalary FROM Employees;

Q3. Explain any four string functions - 4 Marks

string functions in SQL, which are used to manipulate and retrieve information from string data:

1. CONCAT()

• Purpose: Combines two or more strings into one.
• Syntax:

SELECT CONCAT(string1, string2, ...) AS result;

• Example:

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;

• Output:

Greeting

----------

Hello World

2. SUBSTRING() or SUBSTR()

• Purpose: Extracts a portion of a string.
• Syntax:

SELECT SUBSTRING(string, start_position, length) AS result;

• Example:

SELECT SUBSTRING('Hello World', 1, 5) AS SubstringResult;

• Output:

SubstringResult

---------------

Hello

• Explanation: Extracts the substring starting at position 1 for 5 characters.

3. LENGTH()

• Purpose: Returns the length of a string.
• Syntax:

SELECT LENGTH(string) AS StringLength;

• Example:

SELECT LENGTH('SQL Tutorial') AS StringLength;

• Output:

StringLength

------------

12

4. LOWER() and UPPER()

• Purpose: Converts all characters in a string to lowercase (LOWER()) or uppercase (UPPER()).
• Syntax:

SELECT LOWER(string) AS LowerString, UPPER(string) AS UpperString;

• Example:

SELECT LOWER('Hello World') AS LowerCase, UPPER('Hello World') AS UpperCase;

• Output:

LowerCase     | UpperCase

--------------|-----------

hello world   | HELLO WORLD

These string functions are essential for formatting, manipulating, and retrieving specific portions of text data in SQL queries.

 ---------------------------------------------------------------------------------------------

Sample Answer 2

1. LEFT() and RIGHT()

• LEFT(): Extracts a specified number of characters from the beginning of a string.
• RIGHT(): Extracts a specified number of characters from the end of a string.

Example:

SELECT LEFT('Hello World', 5) AS LeftText, RIGHT('Hello World', 5) AS RightText;

2. INSTR() (or POSITION() in some databases)

·        Returns the position of the first occurrence of a substring within a string.

Example:

SELECT INSTR('Hello World', 'World') AS Position;

3. REPLACE()

·        Replaces occurrences of a specified substring within a string with another substring.

Syntax:

REPLACE(string, old_substring, new_substring)

Example :

SELECT REPLACE('Hello World', 'World', 'SQL') AS ReplacedText;

4. TRIM(), LTRIM(), and RTRIM()

·        TRIM(): Removes leading and trailing spaces from a string.

·        LTRIM(): Removes leading spaces.

·        RTRIM(): Removes trailing spaces.

Example :

SELECT TRIM('   Hello World   ') AS TrimmedText;

Q4. Explain any two DDL commands along with example - 4 Marks

Data Definition Language (DDL) commands in SQL are used to define and modify the structure of a database and its objects (like tables, indexes, and views). Here are two important DDL commands:

1. CREATE

• Purpose: Used to create a new database object such as a table, database, index, or view.
• Example: Creating a table named Employees

CREATE TABLE Employees (

 EmployeeID INT PRIMARY KEY,

Name VARCHAR(50),

Age INT,

Department VARCHAR(50)

)

• The CREATE TABLE statement creates a table called Employees.
• It defines columns such as EmployeeID (integer, primary key), Name (variable-length string), Age (integer), and Department (variable-length string).

2. ALTER

• Purpose: Used to modify an existing database object, such as adding or removing columns from a table or modifying its attributes.
• Example: Adding a new column Salary to the Employees table.

ALTER TABLE Employees ADD Salary DECIMAL(10, 2);

The ALTER TABLE command modifies the structure of the Employees table by adding a new column Salary with data type DECIMAL(10, 2) (allowing up to 10 digits, with 2 decimal places).

Example for removing a column:

• Example: Removing the Age column from the Employees table.

ALTER TABLE Employees DROP COLUMN Age;

• CREATE: Defines and creates new objects (e.g., tables, databases).
• ALTER: Modifies existing objects (e.g., adding or removing columns in a table).

These DDL commands help manage the structure and schema of the database.

Q5 . Describe the concept of view with example. State its purpose. 4 Marks

A view in SQL is a virtual table that is based on the result of a SELECT query. It doesn't store data itself but displays data dynamically from one or more base tables. Views are primarily used for simplifying complex queries, enhancing security, and providing a layer of abstraction in the database.

Key Characteristics of a View:

• Virtual Table: A view behaves like a table but does not physically store data. It is derived from a query.
• Abstraction: Views hide the complexity of the database schema by providing a simplified, logical representation of the data.
• Read-only or Updatable: Some views are read-only, while others may allow updates to the underlying tables (with restrictions).
• Security: Views can be used to restrict access to specific rows or columns in a table by exposing only selected data.

Syntax –

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

Example

CREATE VIEW IT_Employees AS SELECT EmployeeID, Name, Salary FROM Employees WHERE Department = 'IT';

Usage of the View:

Once the view is created, you can query it just like a regular table:

SELECT * FROM IT_Employees;

Benefits of Using Views:

1.    Simplification: Complex SQL queries can be saved as views and reused, simplifying SQL code.

2.    Security: Views can limit user access to specific data, providing an additional security layer by hiding sensitive information.

3.    Consistency: Views provide a consistent interface to data even if the underlying database schema changes.

4.    Reusability: Views can be reused across different queries, making them efficient for reporting and data aggregation.

Q6. Write syntax for i) Create Index and ii) Drop Index

1. Create Index

The CREATE INDEX statement is used to create an index on one or more columns of a table to speed up queries.

Syntax:

CREATE INDEX index_name ON table_name (column1, column2, ...);

Example:

To create an index on the LastName column in the Employees table:

CREATE INDEX idx_lastname ON Employees (LastName);

2. Drop Index

The DROP INDEX statement is used to delete an existing index.

DROP INDEX index_name ON table_name;

  Creating an Index: Helps speed up data retrieval for queries but may slow down data manipulation operations like INSERT, UPDATE, and DELETE.

  Dropping an Index: Removes the index, which can slow down query performance but speeds up write operations.

Q7. Consider the schema Customer (Cust-id, Cust_name, Cust_addr, Cust_city)

(i) Create a view on Customer (Cust_id, Cust_name) where Cust_city is 'Pune'

(ii) Create a sequence on Cust_id.

Ans.
(i) CREATE VIEW vwCust (Cust_id, Cust_name) AS Cust_id, Cust_name FROM Customer
WHERE Cust_city = 'Pune'

 above sql statement will create view.

(ii) SQL > CREATE SEQUENCE Cust_id

START WITH 1

INCREMENT BY 1

MAXVALUE 100;

 above sql statement will create sequence. 

Q8. Create table Student (S_id, S_name, S_addr, S_marks) with proper data type and size.

·        (i) Create table student

·        (ii) Insert row  (5, 'ABC', 'RRRRR', 79) into student table.

·        (iii) Update marks of student 85 where S_id is 5

Ans.

(i) 

CREATE table Student(S_id number (4) Primary key,

S_name varchar2(10) NOT NULL,

S_addr varchar2 NOT NULL,

S_marks number(4)(10));

This will create Student table.

(ii) INSERT into Student Values (5, "Avdhut, "Gupte", 79);

This inserts a new row with the values 5, Avdhut, gupte,79 into the Student table.

(iii)

UPDATE Student

SET S_marks= 85

WHERE S_id=5;

This updates Student table with S_marks = 85 where S_id is 5.

Q9. Write syntax for creating and renaming a table

syantax

CREATE TABLE table_name (

 column1 datatype [constraints],

column2 datatype [constraints],

column3 datatype [constraints], ... );

Example

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    hire_date DATE,

    salary DECIMAL(10, 2)

);

ALTER TABLE to rename the name of the table. SQL ALTER TABLE is a command used to modify the structure of an existing table in a database.

Syntax

ALTER TABLE old_table_name RENAME TO new_table_name;

Example

ALTER TABLE employees RENAME TO staff;

Q10. Enlist arithmetic and logical SQL operators

SQL operators are used to perform operations on data in SQL statements. Here are examples of different classes of operators:

1. Addition (+)

   sql

   SELECT Salary, Salary + 1000 AS NewSalary

   FROM Employees;

2. Subtraction (-)

   sql

   SELECT Salary, Salary - 500 AS AdjustedSalary

   FROM Employees;

3. Multiplication (*)

   sql

   SELECT Salary, Salary * 1.1 AS IncreasedSalary

   FROM Employees;

4. Division (/)

   sql

   SELECT Salary, Salary / 2 AS HalfSalary

   FROM Employees;

5. Modulus (%)

   sql

   SELECT EmployeeID, Salary % 1000 AS SalaryRemainder

   FROM Employees;

Logical Operators

1. AND

   sql

   SELECT * FROM Employees

   WHERE DepartmentID = 3 AND Salary > 40000;

2. OR

   sql

   SELECT * FROM Employees

   WHERE DepartmentID = 3 OR Salary > 40000;

   3. NOT

   sql

   SELECT * FROM Employees

   WHERE NOT DepartmentID = 3;

Q11. Write SQL queries for following: 2Marks

(1) Create table student with following attribute using suitable data types. Roll no., as primary key, name, marks as not null and city.

(ii) Add column Date of Birth in above student table.

(iii) Increase the size of attribute name by 10 in above student table.

(iv) Change name of Student table to stud.

Ans.

1) 

CREATE TABLE Student

(Rollno int PRIMARY KEY,

name varchar(30) NOT NULL,

marks int NOT NULL,

city varchar(20) );

 

(ii) ALTER TABLE student ADD DateofBirth varchar(20);

 

(iii) ALTER TABLE student Modify name varchar(48);

 

(iv) RENAME Student to Stud;

 

Q12. Write and Explain the syntax for creating and dropping indexes with an example.

Ans. In SQL, an index is created to improve the performance of queries. Indexes allow the database to quickly locate and retrieve data without having to scan every row in a table.

Syntax for Creating an Index:

CREATE INDEX index_name

ON table_name (column_name1 [, column_name2, ...]);

  CREATE INDEX: The command to create an index.

  index_name: The name you assign to the index (used to reference it later).

  table_name: The name of the table where the index will be created.

  column_name1, column_name2, ...: The columns in the table that will be used for indexing.

Example

CREATE INDEX idx_employee_lastname

ON Employees (LastName);

CREATE INDEX idx_employee_lastname ON Employees (LastName);

  This creates an index idx_employee_lastname on the LastName column of the Employees table.

  After this, queries searching by LastName (e.g., SELECT * FROM Employees WHERE LastName = 'Smith';) will be faster because the database can use the index to find records quickly.

Dropping an Index:

If an index is no longer needed, or if it negatively impacts performance (e.g., during insert/update operations), it can be dropped.

Syntax:

DROP INDEX index_name ON table_name;

DROP INDEX idx_employee_lastname ON Employees;

This drops the idx_employee_lastname index from the Employees table.

Q13. State the use of group by and order by clauses.

Ans. Both GROUP BY and ORDER BY are commonly used in SQL queries but serve different purposes.

1. GROUP BY Clause:

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, often combined with aggregate functions like COUNT(), SUM(), AVG(), etc.

Purpose of GROUP BY:

• Summarize data by grouping similar values in a column (or columns).
• Often used with aggregate functions (COUNT(), SUM(), AVG(), MIN(), MAX()).

Syntax:

sql

SELECT column_name1, aggregate_function(column_name2)

FROM table_name

GROUP BY column_name1;

• column_name1: The column(s) whose values are used to group the results.
• aggregate_function: A function that operates on the grouped data (e.g., SUM(), COUNT()).

Example:

sql

SELECT Department, COUNT(EmployeeID)

FROM Employees

GROUP BY Department;

• This query groups the data by Department and counts how many employees belong to each department.
• Output might look like this:

Department    | Employee Count

------------------------------

HR            | 5

IT            | 12

Sales         | 8

---

2. ORDER BY Clause:

The ORDER BY clause is used to sort the result set of a query by one or more columns in either ascending (ASC) or descending (DESC) order.

Purpose of ORDER BY:

• Sort the data either numerically or alphabetically.
• Can be used with or without other clauses like GROUP BY, WHERE, or JOIN.

Syntax:

sql

SELECT column_name1, column_name2

FROM table_name

ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC];

• column_name1: The column by which the result set will be sorted.
• ASC (default): Sorts in ascending order (A-Z or 1-10).
• DESC: Sorts in descending order (Z-A or 10-1).

Example:

sql

SELECT FirstName, LastName, Salary

FROM Employees

ORDER BY Salary DESC;

• This query selects the FirstName, LastName, and Salary columns from the Employees table and sorts the results in descending order based on Salary.

Output:

FirstName  | LastName  | Salary

-------------------------------

John       | Doe       | 90000

Jane       | Smith     | 85000

Mike       | Johnson   | 75000

---

Combining GROUP BY and ORDER BY:

You can use GROUP BY and ORDER BY together to group data and then sort the result.

Example:

sql

SELECT Department, COUNT(EmployeeID) AS EmployeeCount

FROM Employees

GROUP BY Department

ORDER BY EmployeeCount DESC;

• First, the query groups employees by department and counts how many are in each department.
• Then, it orders the result by the employee count in descending order.

Output:

Department   | EmployeeCount

----------------------------

IT           | 12

Sales        | 8

HR           | 5

---

Key Differences:

• GROUP BY: Used to group rows that share a common value.
• ORDER BY: Used to sort the result set based on column values.

Q14. Write the SQL Queries for following Emp Table. Emp (empno, deptno, ename, salary, designation, city)

1. ·        Display average salary of employees
2. ·        Display names of employees who stay in Mumbai or Pune
3. ·        Set the salary of Employee ‘Ramesh’ to 50000
4. ·        Remove the records of employees whose deptno is 10
5. ·        Remove the column deptno from Emp table.

Ans

(i) SELECT AVG(salary) FROM emp;

(ii) SELECT ename FROM emp WHERE city = 'Mumbai' OR city='Pune';

(iii) UPDATE emp SET salary=50000 WHERE ename="Ramesh";

(iv) SELECT ename FROM exp WHERE salary<50000;

(v) DELETE FROM emp WHERE deptno=10;

(vi) ALTER TABLE emp DROP column deptno;

Q15. Write and Explain the syntax for creating, altering and dropping the sequence.

Ans:

A sequence is a database object in SQL that generates a sequence of unique numbers, which are often used to create unique values for primary keys. Sequences can be incremented automatically as new rows are inserted into a table.

---

1. Creating a Sequence

The CREATE SEQUENCE statement is used to create a new sequence object.

Syntax for Creating a Sequence:

sql

CREATE SEQUENCE sequence_name

    [START WITH initial_value]

    [INCREMENT BY increment_value]

    [MINVALUE min_value | NO MINVALUE]

    [MAXVALUE max_value | NO MAXVALUE]

    [CYCLE | NO CYCLE]

    [CACHE cache_value | NO CACHE];

• sequence_name: The name of the sequence.
• START WITH: Specifies the starting value of the sequence (default is 1).
• INCREMENT BY: Specifies the value by which the sequence is incremented (can be positive or negative). Default is 1.
• MINVALUE: The minimum value of the sequence (optional).
• MAXVALUE: The maximum value of the sequence (optional).
• CYCLE: If set, the sequence will start again from the minimum value after reaching the maximum value. If NO CYCLE is used, the sequence will stop once the maximum is reached.
• CACHE: Specifies how many sequence values are preallocated and stored in memory for faster access. Default is 20.

Example of Creating a Sequence:

sql

CREATE SEQUENCE employee_seq

    START WITH 1

    INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 10000

    CYCLE;

• employee_seq: The sequence starts at 1 and increments by 1 for each new value.
• It will cycle back to 1 after reaching 10,000.
• This sequence can be used to automatically generate unique EmployeeID values.

---

2. Altering a Sequence

You can modify an existing sequence using the ALTER SEQUENCE statement. This allows you to change certain properties of the sequence without recreating it.

Syntax for Altering a Sequence:

sql

ALTER SEQUENCE sequence_name

    [RESTART [WITH restart_value]]

    [INCREMENT BY increment_value]

    [MINVALUE min_value | NO MINVALUE]

    [MAXVALUE max_value | NO MAXVALUE]

    [CYCLE | NO CYCLE]

    [CACHE cache_value | NO CACHE];

• RESTART: Restarts the sequence at a specified value (if WITH is used) or at the current value.
• INCREMENT BY: Changes the step value by which the sequence increments or decrements.
• MINVALUE, MAXVALUE: Adjusts the minimum and maximum allowable values.
• CYCLE, NO CYCLE: Changes whether the sequence should cycle or not.
• CACHE, NO CACHE: Modifies how many sequence numbers are preallocated.

Example of Altering a Sequence:

sql

ALTER SEQUENCE employee_seq

    RESTART WITH 1000

    INCREMENT BY 5;

• This restarts the sequence at 1000, and each subsequent value will increment by 5 (i.e., 1000, 1005, 1010, etc.).

3. Dropping a Sequence

To remove a sequence, use the DROP SEQUENCE statement. This permanently deletes the sequence object from the database.

Syntax for Dropping a Sequence:

sql

DROP SEQUENCE sequence_name;

• sequence_name: The name of the sequence to drop.

Example of Dropping a Sequence:

sql

DROP SEQUENCE employee_seq;

• This deletes the employee_seq sequence. After this, the sequence will no longer be available for generating new values.
  
  

Q16. Write SQL queries for following. Consider table stud (roll no, name, subl, sub2, sub3).

(1) Display nines of student who got minimum mark in subl.

(ii) Display names of students who got above 40 marks in sub2.

(iii) Display count of Students failed in sub2.

(iv) Display average marks of subl of all students.

(v) Display names of students whose name start with 'A' by arranging them in ascending order of subl marks

(vi) Display student name whose name ends with h' and subject 2 marks are between 60 to 75. 6

Ans:

(1) SELECT name FROM stud WHERE sub1 (SELECT MIN(sub1) FROM stud);

(ii) SELECT name FROM stud WHERE sub2>40;

(iii) SELECT COUNT(*) FROM stud WHERE sub2<40;

(iv) SELECT AVG(sub1) FROM stud;

(v) SELECT name FROM stud WHERE name like 'A%' ORDER BY sub1;

(vi) SELECT name FROM stud WHERE name like '%h' AND sub2 BETWEEN 60 and 75;

Summer 2023

Q17. Explain any four aggregate functions with example.

Ans. Aggregate functions in SQL perform calculations on a set of values and return a single value. These functions are commonly used with the GROUP BY clause and are essential for summarizing data. Let’s look at four of the most commonly used aggregate functions with examples.

1. COUNT()

The COUNT() function is used to count the number of rows or non-null values in a column.

Syntax:

sql

SELECT COUNT(column_name)

FROM table_name

WHERE condition;

• COUNT(column_name): Counts the number of non-NULL values in the specified column.
• COUNT(*): Counts all rows, including those with NULL values.

Example:

sql

SELECT COUNT(EmployeeID)

FROM Employees

WHERE Department = 'HR';

• This query counts how many employees are in the HR department.

Result:

COUNT(EmployeeID)

----------------

5

---

2. SUM()

The SUM() function calculates the total sum of a numeric column.

Syntax:

sql

SELECT SUM(column_name)

FROM table_name

WHERE condition;

• SUM(column_name): Adds up all the values in the specified column (only works for numeric data types).

Example:

sql

SELECT SUM(Salary)

FROM Employees

WHERE Department = 'Sales';

• This query calculates the total sum of salaries for employees in the Sales department.

Result:

SUM(Salary)

-----------

200000

3. AVG()

The AVG() function calculates the average value of a numeric column.

Syntax:

sql

SELECT AVG(column_name)

FROM table_name

WHERE condition;

• AVG(column_name): Computes the average value of the numeric column.

Example:

sql

SELECT AVG(Salary)

FROM Employees

WHERE Department = 'IT';

• This query returns the average salary of employees in the IT department.

Result:

AVG(Salary)

------------

75000

4. MAX()

The MAX() function returns the maximum (largest) value in a column.

Syntax:

sql

SELECT MAX(column_name)

FROM table_name

WHERE condition;

• MAX(column_name): Returns the highest value in the specified column.

Example:

sql

SELECT MAX(Salary)

FROM Employees;

• This query returns the highest salary among all employees.

Result:

MAX(Salary)

------------

90000

Q18. Explain any four DML commands with syntax and example.

Ans. DML commands in SQL are used to manipulate data in the database. They allow you to insert, update, delete, and retrieve data. Let's discuss four common DML commands: INSERT, UPDATE, DELETE, and SELECT.

---

1. INSERT: Add New Data to a Table

The INSERT command is used to add new rows of data into a table.

Syntax:

sql

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

• table_name: The name of the table where you want to insert the data.
• column1, column2, ...: The columns for which you are providing values.
• value1, value2, ...: The values to be inserted in the corresponding columns.

Example:

sql

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)

VALUES (101, 'John', 'Doe', 'HR', 60000);

• This inserts a new row with the values 101, John, Doe, HR, and 60000 into the Employees table.

---

2. UPDATE: Modify Existing Data

The UPDATE command is used to modify existing rows in a table based on a specified condition.

Syntax:

sql

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

• table_name: The name of the table where you want to update data.
• SET column1 = value1: Specifies the columns to be updated and their new values.
• WHERE condition: Specifies which rows should be updated (if omitted, all rows will be updated).

Example:

sql

UPDATE Employees

SET Salary = 65000

WHERE EmployeeID = 101;

• This query updates the Salary of the employee with EmployeeID = 101 to 65000.

---

3. DELETE: Remove Data from a Table

The DELETE command is used to delete existing rows from a table based on a specified condition.

Syntax:

sql

DELETE FROM table_name

WHERE condition;

• table_name: The name of the table from which you want to delete data.
• WHERE condition: Specifies which rows should be deleted (if omitted, all rows will be deleted).

Example:

sql

DELETE FROM Employees

WHERE EmployeeID = 101;

• This query deletes the row where the EmployeeID is 101 from the Employees table.

---

4. SELECT: Retrieve Data from a Table

The SELECT command is used to retrieve data from a table. You can select specific columns or all columns.

Syntax:

sql

SELECT column1, column2, ...

FROM table_name

WHERE condition;

• column1, column2, ...: The columns you want to retrieve (use * to select all columns).
• table_name: The table from which to retrieve the data.
• WHERE condition: Filters rows based on the specified condition (optional).

Example:

sql

SELECT FirstName, LastName, Salary

FROM Employees

WHERE Department = 'HR';

• This query retrieves the FirstName, LastName, and Salary of employees in the HR department.
  
  

Q19. Explain any two types of join with example.

Ans. A join in SQL is used to combine rows from two or more tables based on a related column between them. Let's discuss two common types of joins: INNER JOIN and LEFT JOIN, along with examples.

---

1. INNER JOIN

The INNER JOIN returns records that have matching values in both tables. If there is no match between the two tables, the rows are excluded from the result.

Syntax:

sql

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

• INNER JOIN: Combines rows from both tables where the join condition is satisfied (i.e., matching values in both tables).
• ON: Specifies the condition for the join.

Example:

Consider two tables:

·        Employees:

EmployeeID | FirstName | DepartmentID

--------------------------------------

101        | John      | 1

102        | Jane      | 2

103        | Mike      | 1

·        Departments:

DepartmentID | DepartmentName

------------------------------

1            | HR

2            | IT

Query:

sql

SELECT Employees.FirstName, Departments.DepartmentName

FROM Employees

INNER JOIN Departments

ON Employees.DepartmentID = Departments.DepartmentID;

• This INNER JOIN matches rows in the Employees table with rows in the Departments table where the DepartmentID values are the same.

Result:

FirstName  | DepartmentName

----------------------------

John       | HR

Jane       | IT

Mike       | HR

• In this result, only employees with a matching DepartmentID in both tables are shown.

---

2. LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN returns all records from the left table (the table mentioned first) and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.

Syntax:

sql

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.column = table2.column;

• LEFT JOIN: Returns all rows from the left table and the matching rows from the right table. If no match is found, the right table’s columns will contain NULL.
• ON: Specifies the condition for the join.

Example:

Using the same Employees and Departments tables:

·        Employees:

EmployeeID | FirstName | DepartmentID

--------------------------------------

101        | John      | 1

102        | Jane      | 2

103        | Mike      | 1

104        | Sarah     | 3

·        Departments:

DepartmentID | DepartmentName

------------------------------

1            | HR

2            | IT

Query:

sql

SELECT Employees.FirstName, Departments.DepartmentName

FROM Employees

LEFT JOIN Departments

ON Employees.DepartmentID = Departments.DepartmentID;

• This LEFT JOIN returns all rows from the Employees table, along with matching rows from the Departments table. If a match is not found, the department name will be NULL.

Result:

FirstName  | DepartmentName

----------------------------

John       | HR

Jane       | IT

Mike       | HR

Sarah      | NULL

• The employee Sarah is shown even though her DepartmentID does not match any in the Departments table. The DepartmentName for her is NULL because there is no corresponding department.

---

Key Differences Between INNER JOIN and LEFT JOIN:

Feature	INNER JOIN	LEFT JOIN
Returned Rows	Only rows with matching values in both tables	All rows from the left table, and matched rows from the right table
Rows without a match	Excluded	Included with NULL values for unmatched rows
Use Case	When you want only records that exist in both tables	When you want all records from the left table, regardless of match

 

Q20. Enlist types of SQL Joins.

Ans.

In SQL, joins are used to combine rows from two or more tables based on a related column between them. Here are the different types of SQL joins:

1.    INNER JOIN:

o   Returns only the rows that have matching values in both tables.

o   Syntax:

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

2.    LEFT (OUTER) JOIN:

o   Returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.

o   Syntax:

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.column = table2.column;

3.    RIGHT (OUTER) JOIN:

o   Returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.

o   Syntax:

SELECT columns

FROM table1

RIGHT JOIN table2

ON table1.column = table2.column;

4.    FULL (OUTER) JOIN:

o   Returns all rows when there is a match in either table. If there is no match, NULL values are returned for the columns from the table without a match.

o   Syntax:

SELECT columns

FROM table1

FULL OUTER JOIN table2

ON table1.column = table2.column;

5.    CROSS JOIN:

o   Returns the Cartesian product of the two tables (i.e., every row from the first table is combined with every row from the second table).

o   Syntax:

SELECT columns

FROM table1

CROSS JOIN table2;

6.    SELF JOIN:

o   A join in which a table is joined with itself, typically to compare rows within the same table.

o   Syntax:

SELECT a.columns, b.columns

FROM table1 a

JOIN table1 b

ON a.column = b.column;

7.    NATURAL JOIN:

o   Automatically joins tables based on columns with the same name and data type in both tables. No need to specify the column explicitly.

o   Syntax:

SELECT columns

FROM table1

NATURAL JOIN table2;

These are the primary types of joins used in SQL to retrieve data from multiple tables based on relationships between them.

 

Q21. Explain commit and rollback with syntax and example.

In SQL, COMMIT and ROLLBACK are used to manage transactions. A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. These statements are used to control whether the changes made by the transaction are saved permanently or undone.

1. COMMIT:

• Definition: The COMMIT statement is used to save the changes made by a transaction permanently to the database.
• Once a COMMIT is issued, the changes cannot be undone by a ROLLBACK.
• Syntax:

COMMIT;

• Example: Suppose you are inserting a new record into the employees table.

sql

BEGIN TRANSACTION;

 

INSERT INTO employees (id, name, department)

VALUES (101, 'John Doe', 'Sales');

 

COMMIT;

After executing COMMIT, the new record is permanently saved in the database.

2. ROLLBACK:

• Definition: The ROLLBACK statement is used to undo the changes made by a transaction. If something goes wrong during the transaction (e.g., incorrect data), you can use ROLLBACK to restore the database to its previous state before the transaction started.
• Syntax:

ROLLBACK;

• Example: Suppose you are updating a record, but you realize there's a mistake before you commit the changes:

sql

 

BEGIN TRANSACTION;

 

UPDATE employees

SET department = 'Marketing'

WHERE id = 101;

 

-- Oops! Realized this is a mistake

ROLLBACK;

After executing ROLLBACK, the changes made in the UPDATE statement will be undone, and the employee's department will not change.

Key Points:

• Transactions typically start with BEGIN TRANSACTION (or are implicit in some databases).
• All changes made within a transaction are temporary until a COMMIT is executed.
• ROLLBACK can undo any uncommitted changes, but once a transaction is committed, you cannot roll it back.

Transaction Flow Example:

sql

BEGIN TRANSACTION;

UPDATE accounts

SET balance = balance - 100

WHERE account_id = 1;

UPDATE accounts

SET balance = balance + 100

WHERE account_id = 2;

-- If everything is correct, commit the transaction

COMMIT;

Alternatively, if an error occurs:

sql

BEGIN TRANSACTION;

UPDATE accounts

SET balance = balance - 100

WHERE account_id = 1;

UPDATE accounts

SET balance = balance + 100

WHERE account_id = 2;

-- If an error occurs, undo the transaction

ROLLBACK;

This ensures data consistency and reliability in case of errors or unexpected issues during the transaction.

Q22. Write SQL query for the following designation, joining date, DOB, city). List employees with having alphabate 'A' as second letter in their name.

(i) consider table. Emp (emp_id, dept, emp_name, salary,

(ii) Set salary of all project leaders to 50,000.

(iii) Display average salary of all employees.

(iv) Display employee-Id of employees who live city 'Pune' or 'Nagpur

(v) Display employee name, salary and city from 'HR' department who having salary greater than 70,000.

(vi) Display details of all employee who's salary same as that of salary of 'Shashank

Ans.

(i) SELECT FROM Emp WHERE emp name LIKE 'A%X';

 

(ii) UPDATE Emp SET salary=50000

     WHERE designation = 'project leader';

 

(iii) SELECT AVG(salary) FROM Emp;

 

(iv)

SELECT emp_id FROM Emp

WHERE city 'Pune' OR city='Nagpur';

(v)

SELECT emp_name, salary, city

FROM Emp

WHERE dept HR AND salary > 70000;

(vi)

SELECT FROM Emp

WHERE salary IN (

select salary FROM EMP WHERE emp_name='Shashank');

 

Q23. Consider schema of book table as Book Master (book_id, book_name, author, no_of_copies, price)

Write down the SQL queries for the following:

(i) Write a query to create table Book_Master table.

(ii) Write a command to create composite index on Book_Master table.

(iii) Write a command to drop above index.

(iv) Display book name and author in decreasing order of price.

(v) Display all books whose number of copies are greater than 50.

(vi) Create synonym for relation Book_Master as Book_Information.

Ans.

·        CREATE table Book_Master

       (book_id int(4,0),

       book_name varchar(50),

       author varchar(50),

      no_of_copies (2,0),

      price int (6,2));

 

(ii) CREATE INDEX composite_book_master ON Book_Master(book_id, book_name);

(iii) DROP INDEX composite_book_master;

Interactive SQL and Performance Turing

(iv) SELECT book_name, author FROM Book Master ORDER BY price desc;

(v) SELECT * FROM Book Master WHERE no_of_copies >50;

(VÍ) CREATE OR REPLACE public SYNONYM Book Information FOR Book Master;

Summer 2024

Q24. List date and time functions

Ans.

SQL provides a wide range of functions to work with date and time values. These functions are useful for extracting, manipulating, and formatting date and time data.

Here are some commonly used date and time functions in SQL:

1. CURRENT_DATE

• Returns the current date (without time).
• Syntax:

sql

 

SELECT CURRENT_DATE;

• Example:

sql

 

SELECT CURRENT_DATE;  -- Output: 2024-10-14 (depends on current date)

2. CURRENT_TIME

• Returns the current time (without the date).
• Syntax:

sql

 

SELECT CURRENT_TIME;

• Example:

sql

 

SELECT CURRENT_TIME;  -- Output: 14:30:25 (depends on current time)

3. CURRENT_TIMESTAMP

Returns the current date and time.

Syntax:

sql

 

SELECT CURRENT_TIMESTAMP;

Example:

sql

 

SELECT CURRENT_TIMESTAMP;  -- Output: 2024-10-14 14:30:25 (depends on current date and time)

4. GETDATE() (SQL Server)

Returns the current date and time.

Syntax:

sql

 

SELECT GETDATE();

Example:

sql

 

SELECT GETDATE();  -- Output: 2024-10-14 14:30:25.123

5. DATEPART() (SQL Server)

Extracts a specific part of a date (year, month, day, hour, etc.).

Syntax:

sql

 

SELECT DATEPART(part, date);

Example:

sql

 

SELECT DATEPART(year, '2024-10-14'); 

-- Output:

2024

SELECT DATEPART(month, GETDATE());  

 -- Output:

10 (October)

6. DATEADD() (SQL Server)

Adds a specific interval (days, months, years, etc.) to a date.

Syntax:

sql

SELECT DATEADD(part, value, date);

Example:

sql

   SELECT DATEADD(day, 7, '2024-10-14');  -- Output: 2024-10-21

7. DATEDIFF() (SQL Server)

Calculates the difference between two dates.

Syntax:

sql

SELECT DATEDIFF(part, start_date, end_date);

Example:

sql

SELECT DATEDIFF(day, '2024-10-01', '2024-10-14');  -- Output: 13 days

Q25. Differentiate between delete and truncate command with example.

Ans. The DELETE and TRUNCATE commands in SQL are both used to remove records from a table, but they differ significantly in their functionality, performance, and usage. Here's a breakdown of the key differences:

1. DELETE Command:

• Purpose: Removes specific rows or all rows from a table based on a condition.
• Transaction Control: It is transactional, meaning you can ROLLBACK the changes if necessary (if the transaction hasn't been committed yet).
• Where Clause: Can be used with a WHERE clause to delete specific records.
• Logging: It is a row-by-row operation, and each deleted row is logged in the transaction log.
• Slower: Due to logging and row-by-row deletion, it is slower compared to TRUNCATE.
• Table Structure: The table's structure and indexes are not affected by DELETE.

Syntax:

sql

DELETE FROM table_name WHERE condition;

Example:

sql

-- Delete specific rows from the 'employees' table where department is 'Sales'

DELETE FROM employees

WHERE department = 'Sales';

sql

-- Delete all rows from the 'employees' table

DELETE FROM employees;

2. TRUNCATE Command:

• Purpose: Removes all rows from a table.
• Transaction Control: It is non-transactional in some databases (cannot be rolled back in systems like MySQL, though it can be rolled back in databases like SQL Server if wrapped in a transaction).
• Where Clause: Cannot be used with a WHERE clause, since it removes all rows from the table.
• Logging: It is minimally logged (only the deallocation of data pages is logged), making it faster than DELETE.
• Faster: Performs a bulk deletion without logging individual row deletions, making it much faster for large datasets.
• Table Structure: TRUNCATE resets table-related properties like the auto-increment counter (in some databases), but it doesn't affect the structure, constraints, or indexes.

Syntax:

sql

TRUNCATE TABLE table_name;

Example:

sql

-- Truncate the 'employees' table, removing all rows quickly

TRUNCATE TABLE employees;

---------------------------------------------------------------------------------------------

Key Differences:

Feature	DELETE	TRUNCATE
Purpose	Deletes specific or all rows	Removes all rows from a table
Use of WHERE clause	Can use a WHERE clause to delete specific rows	Cannot use a WHERE clause (removes all rows)
Transaction	Transactional, can be rolled back	Non-transactional (in some databases), cannot be rolled back
Logging	Fully logged (slower, row-by-row)	Minimally logged (faster, page deallocation)
Speed	Slower for large datasets	Faster for large datasets
Auto-increment reset	Does not reset auto-increment counter	Resets auto-increment counter (in some DBs)
Table structure	Preserves structure, triggers, indexes	Preserves structure but resets auto-increment
Foreign Key Dependency	Can be used with foreign keys (with cascading)	Might need to disable foreign key constraints

Example:

DELETE Example:

sql

-- Deleting rows where department is 'IT'

DELETE FROM employees

WHERE department = 'IT';

TRUNCATE Example:

sql

-- Truncating the entire employees table

TRUNCATE TABLE employees;

 

Q26. Write a query to create table emp-details (Emp_ID, name, dept) with proper data types and Emp_ID as primary key and name as NOT NULL constrain. il) Perform all DML command on above created table.

Ans.

(i)Write a query to create table emp-details (Emp_ID, name, dept) with proper data types and Emp_ID as primary key and name as NOT NULL constraints

CREATE table emp_details

(Emp_IDnumber(4) Primary key,

Name varchar2(10) not null,

Dept varchar2(10));

OR

CREATE table emp_details

(Emp_IDnumber (4) constraint PK Primary key,

Name varchar2(10) constraint NN NOT NULL,

Dept varchar2(10));

 

Perform all DML command on above created table.

> INSERT INTO emp_details Values (llll, ‘Rajan’, ‘Computer’);

> SELECT FROM emp_detalls WHERE emp_ID=1111;

> UPDATE emp_details

SET name = ‘Rajan’

WHERE emp_id = 1311;

 

> DELETE FROM emp_details;

 

Q26. Consider following schema:

Book-master (book, book_name, sudur, no_of_copies, price)

Write down SQL queries for following

(i) Write a command to create Book_Master table

(ii) Increase the price of all books by 20%

(iii) Display all books whose price ranges from 500 to 800

(iv) Display all books with details whose name start with "D

(v) Display all books whose order of copies are less than 10.

(vi) Display all books whose price is above 700.

Ans.

SOL>

(i) Write a command to create Book Master table.
CREATE table Book_Master

(bookid int (5) primary key,

Bookname varchar2 (10),

Author varchar2 (20),

no_of_copies int(10),

price int(10,2) );

 

(ii) Increase the price of all books by 20%.

SQL>UPDATE Book_Master Set price=price+(price*0.2);

 

(iii) Display all books whose price is between Rs.500 and Rs. 800.

SQL> SELECT * FROM Book_Master WHERE price BETWEEN 500 and 800;

 

(iv) Display all books with details whose name start with 'D'.

SQL> SELECT bookname FROM Book_Master WHERE bookname LIKE ‘D%’;

 

(v) Display all books whose number of copies are less than 10. SQL>SELECT * FROM Book_Master WHERE no_of_copies<10;

 

(vi) Display all books whose price is above Rs. 700

SQL>SELECT * FROM Book Master WHERE price > 700;