PRACTICAL:1

AIM: Create sample database table.

 PRACTICAL:2
AIM: Write sql query to create table and insert 10 records.

SYNTAX:

INSERT INTO TABLE_NAME (column1, column2...columnN)

VALUES (value1, value2...valueN);

OR

INSERT INTO TABLE_NAME

VALUES (value1,value2...valueN);

Example:

CREATE TABLE CUSTOMERS(

ID INT ,

NAME VARCHAR (20) NOT NULL,

AGE INT ,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, bhumi’’, 22, 'Ahmedabad', 50000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, 'disha', 25, 'Delhi', 50000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, 'jinal', 28, 'Kota', 40000.00 );

 PRACTICAL:4
AIM: Write sql query to delete the particular table.

Syntax:

DELETE FROM table_name;

Ex: delete from customer;

With condition:

DELETE FROM table_name

WHERE condition;

DELETE FROM table_name

WHERE condition1 AND condition2 OR ... conditionN;

Ex: delete from customer where name='hemang' AND age=25 OR id=6;

Ex: DELETE FROM CUSTOMERS

WHERE NAME='Komal' OR ADDRESS='Mumbai';

Note: In delete the data will be deleted not whole table.

If you want to delete whole table, use drop query.

 PRACTIAL 6

AIM: Write sql queries to use various numeric functions

1 ABS()

Returns the absolute value of numeric expression.

2 ACOS()

Returns the arccosine of numeric expression. Returns NULL if the value is

not in the range -1 to 1.

3 ASIN()

Returns the arcsine of numeric expression. Returns NULL if value is not in

the range -1 to 1

4 ATAN()

Returns the arctangent of numeric expression.

5 ATN2()

Returns the arctangent of the two variables passed to it.

6 CEILING()

Returns the smallest (closest to negative infinity) integer value that is

greater than or equal to this value.
7 COS()

Returns the trigonometric cosine of the given value.

8 COT()

Returns the trigonometric cotangent of the given value.

9 DEGREES()

Returns numeric expression converted from radians to degrees.

1 EXP()
0
Returns the base of the natural logarithm (e) raised to the power of passed
numeric expression.

1 FLOOR()
1
Returns the largest integer value that is not greater than passed numeric
expression.

1 LOG()
2
Returns the natural logarithm of the passed numeric expression.

1 LOG10()
3
Returns the base-10 logarithm of the passed numeric expression.

1 PI()
4
Returns the value of pi

1 POWER()
5
 Returns the value of one expression raised to the power of another
expression

1 RADIANS()
6
Returns the value of passed expression converted from degrees to radians.

1 RAND()
7
Returns the random value between 0 and 1.

1 ROUND()
8
Returns numeric expression rounded to an integer. Can be used to round
an expression to a number of decimal points

1 SIGN()
9
Returns the sign of a number, indicating whether it is positive, negative, or
zero.

2 SIN()
0
Returns the sine of numeric expression given in radians.

2 SQRT()
1
Returns the non-negative square root of numeric expression.

2 TAN()
2
Returns the tangent of numeric expression expressed in radians.
/*write sql queries to use various numeric function*/

SELECT ABS(-243.5);

SELECT ACOS(0.25);

SELECT ATAN(2.5);

SELECT CEILING(25.75);

SELECT COS(30);

SELECT COT(6);

SELECT DEGREES(1.5);

SELECT EXP(1);

SELECT FLOOR(25.75);

SELECT GREATEST(30, 789, 55, 49, 500);

SELECT LEAST(56, 1, 5, 81, 5);

SELECT LOG(2);

SELECT PI();

SELECT POWER(4, 2);

SELECT RADIANS(180);

SELECT RAND();

SELECT SIGN(255.5);
 PRACTICAL:8

AIM: Write sql queries to use various operators.

/*Write SQL query to use various operators

1. SQL Arithmetic Operators

+ ADDITION
- SUBTRACTION

* MULTIPLICATION

/ DIVISION

% MODULUS

2. comparison operators

Operator Description

= Equal to

< Less than

> Greater than

<= Less than or equal to

>= Greater than or equal to

<>, != Not equal to

Comparison Operators
We can compare two values using comparison operators in SQL.

These operators return either 1 (means true) or 0 (means false).

3. logical operators

Logical Operators

We can use logical operators to compare multiple SQL commands. These operators return either 1 (means
true) or 0 (means false).

Logical operators available in SQL are,

ANY and ALL

AND, OR and NOT

BETWEEN

EXISTS

IN

LIKE

IS NULL

These operators are used with SQL clauses such as:

SELECT, WHERE, ON etc.

*/

CREATE TABLE BILL(ID INT,

NAME VARCHAR(50),

DATE DATE,

AMOUNT INT,

);
INSERT INTO BILL VALUES(101,'BHUMI','2023/12/22',5000),

(101,'FENI','2023/12/22',3500),

(101,'YASH','2023/12/22',1000),

(101,'DEESHA','2023/12/22',6000),

(101,'KINJAL','2023/12/22',2000);

SELECT * FROM BILL;

/*ADDITION*/

SELECT DATE,NAME, AMOUNT, AMOUNT+500 AS TOTAL_AMOUNT FROM BILL;

SELECT * FROM BILL;

/*SUBTRACTIN*/

SELECT ID,NAME,AMOUNT,DATE,AMOUNT-200 AS SUBTRACT_AMOUNT FROM BILL;

/*MUTLIPLICATION*/

SELECT ID,NAME,AMOUNT,AMOUNT*5 AS MUTLIPLICATION FROM BILL;

SELECT ID,NAME,AMOUNT,AMOUNT*5 AS MULTI,AMOUNT-500 AS SUB FROM BILL;

/*DIVISION*/

SELECT ID,NAME,AMOUNT,AMOUNT/5 AS DIV FROM BILL;

/*COMPARISION OPERATORS*/

SELECT NAME,AMOUNT FROM BILL WHERE NAME='FENI';

--Comparison Operators
-- ( > GREATER THAN) returns records where amount is greater than 1000 (exclusive)

SELECT * FROM BILL;

SELECT id, name, amount

FROM BILL

WHERE amount > 1000;

-- returns records where amount is less than or equal to 700

SELECT id, name, amount

FROM BILL

WHERE amount <= 1000;

-- returns records where amount is greater than or equal to 400

SELECT id, name, amount

FROM BILL

WHERE amount >= 6000;

-- returns records where amount is not equal to 900

SELECT id, name,date,amount

FROM BILL

WHERE amount != 3500;

--Logical operators

-- select the NAME and ID of all customers

-- whose name is 'bhumi' and have the id '101'.

SELECT NAME, ID

FROM BILL

WHERE NAME = 'BHUMI' AND ID=101;

-- select first and last name of customers

-- who either live in the USA

-- or have the last name 'Doe'

SELECT NAME, ID

FROM BILL

WHERE NAME = 'BHUMI' OR ID=101;

SELECT * FROM BILL;

ADVANCE
NOT

SELECT * FROM BILL WHERE NOT AMOUNT=1000;

COMBINING NOT & OR

SELECT * FROM BILL WHERE NOT AMOUNT=1000 AND ID=101;

BETWEEN

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

SELECT BRAND_NAME,INT,CUSTOMER_NAME

FROM BRANDS

WHERE PRICE BETWEEN 7000 AND 45000;

SELECT column1, column2, ...

FROM table_name
WHERE columnN LIKE pattern;

SELECT PRICE, BRAND_NAME, CATEGORY,CUSTOMER_NAME

FROM BRANDS
WHERE CUSTOMER_NAME LIKE 'BHUMI';

SELECT column_name(s)

FROM table_name
WHERE expression comparison_operator SOME (subquery)
 PRACTICAL:10

AIM: WRITE QUERY TO EXECUTE VARIOUS GROUP FUNCTION.

CREATE TABLE BRANDS(

INT ID,
BRAND_NAME VARCHAR(20),
PRICE DECIMAL,
CATEGORY VARCHAR(15),
CUSTOMER_NAME VARCHAR(16));

INSERT INTO BRANDS

VALUES(22,'PUMA',7000,'SHOES','BHUMI'),
(11,'NIKE',4000,'T-SHIRT','HEM'),
(21,'TITAN',5000,'WATCH','FENI'),
(44,'APPLE',100000,'IPHONE 14 PRO','VISHAL'),
(77,'ASUS',80000,'STRIX LAPTOP G15','BHUMI'),
(99,'TATA',2500000,'HARRIER','BHUMII');

SUM Returns the total sum

MIN Returns the lowest value

MAX Returns the highest value

AVG Returns the average value

 COUNT (*) Returns the number of records in a table

COUNT (column) Returns the number of values (NULL values will not be
counted) of the specified column

COUNT (DISTINCT Returns the number of distinct values

column)

SELECT MIN(PRICE) FROM BRANDS;

SELECT AVG(PRICE) FROM BRANDS;
SELECT MAX(PRICE) FROM BRANDS;
SELECT SUM(PRICE) FROM BRANDS;
SELECT COUNT(PRICE) FROM BRANDS;
SELECT COUNT(BRAND_NAME) FROM BRANDS;

SELECT column_name(s)FROM table_name

WHERE column_name BETWEEN value1 AND value2;

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

 PRACTICAL:11

AIM: WRITE SQL queries using Group By,Having and Order by

clause.

CREATE TABLE BRANDS(

INT ID,
BRAND_NAME VARCHAR(20),
PRICE DECIMAL,
CATEGORY VARCHAR(15),
CUSTOMER_NAME VARCHAR(16));

INSERT INTO BRANDS

VALUES(22,'PUMA',7000,'SHOES','BHUMI'),
(11,'NIKE',4000,'T-SHIRT','HEM'),
(21,'TITAN',5000,'WATCH','FENI'),
(44,'APPLE',100000,'IPHONE 14 PRO','VISHAL'),
(77,'ASUS',80000,'STRIX LAPTOP G15','BHUMI'),
(99,'TATA',2500000,'HARRIER','BHUMII');
 PRACTICAL:12

AIM: Write SQL queries to create a table.

CREATE TABLE BRANDS(

INT ID,
BRAND_NAME VARCHAR(20),
PRICE DECIMAL,
CATEGORY VARCHAR(15),
CUSTOMER_NAME VARCHAR(16));

EXTRA:

ALTER TABLE table_name

DROP COLUMN column_name;

1.Add new field in existing table

ALTER

ALTER table BRANDS ADD Quantity;

2.ALTER TABLE - RENAME COLUMN

ALTER table BRANDS
RENAME Quantity to Total_Quantity;
 PRACTICAL:13

AIM: Write SQL queries to insert a value into a table.

CREATE TABLE BRANDS(

INT ID,
BRAND_NAME VARCHAR(20),
PRICE DECIMAL,
CATEGORY VARCHAR(15),
CUSTOMER_NAME VARCHAR(16));

INSERT INTO BRANDS

VALUES(22,'PUMA',7000,'SHOES','BHUMI'),
(11,'NIKE',4000,'T-SHIRT','HEM'),
(21,'TITAN',5000,'WATCH','FENI'),
(44,'APPLE',100000,'IPHONE 14 PRO','VISHAL'),
(77,'ASUS',80000,'STRIX LAPTOP G15','BHUMI'),
(99,'TATA',2500000,'HARRIER','BHUMII');
 PRACTICAL:14

AIM: Write SQL queries to show the records in the table.

CREATE TABLE BRANDS(

INT ID,
BRAND_NAME VARCHAR(20),
PRICE DECIMAL,
CATEGORY VARCHAR(15),
CUSTOMER_NAME VARCHAR(16));

INSERT INTO BRANDS

VALUES(22,'PUMA',7000,'SHOES','BHUMI'),
(11,'NIKE',4000,'T-SHIRT','HEM'),
(21,'TITAN',5000,'WATCH','FENI'),
(44,'APPLE',100000,'IPHONE 14 PRO','VISHAL'),
(77,'ASUS',80000,'STRIX LAPTOP G15','BHUMI'),
(99,'TATA',2500000,'HARRIER','BHUMII');

Code:

SELECT * FROM BRANDS;

SELECT CUSTOMER_NAME,price FROM BRANDS;

SELECT CUSTOMER_NAME,
CATEGORY,
ADDRESS,
PRICE
FROM BRANDS WHERE PRICE=7000;
 PRACTICAL:15

AIM: Write SQL queries to show the one field of the table.

CREATE TABLE BRANDS(

INT ID,
BRAND_NAME VARCHAR(20),
PRICE DECIMAL,
CATEGORY VARCHAR(15),
CUSTOMER_NAME VARCHAR(16));

INSERT INTO BRANDS

VALUES(22,'PUMA',7000,'SHOES','BHUMI'),
(11,'NIKE',4000,'T-SHIRT','HEM'),
(21,'TITAN',5000,'WATCH','FENI'),
(44,'APPLE',100000,'IPHONE 14 PRO','VISHAL'),
(77,'ASUS',80000,'STRIX LAPTOP G15','BHUMI'),
(99,'TATA',2500000,'HARRIER','BHUMII');

SELECT BRAND_NAME FROM BRANDS;

SELECT ID,BRAND_NAME FROM BRANDS WHERE ID=22;

 PRACTICAL:16

AIM: Write SQL queries to delete the record in the table.

Deleting records from the table:

DELETE FROM CUSTOMERS

WHERE NAME='Komal' OR ADDRESS='Mumbai';

 PRACTICAL:17

AIM: Write SQL queries to show some records.

CREATE TABLE BRANDS(

INT ID,
BRAND_NAME VARCHAR(20),
PRICE DECIMAL,
CATEGORY VARCHAR(15),
CUSTOMER_NAME VARCHAR(16));

INSERT INTO BRANDS

VALUES(22,'PUMA',7000,'SHOES','BHUMI'),
(11,'NIKE',4000,'T-SHIRT','HEM'),
(21,'TITAN',5000,'WATCH','FENI'),
(44,'APPLE',100000,'IPHONE 14 PRO','VISHAL'),
(77,'ASUS',80000,'STRIX LAPTOP G15','BHUMI'),
(99,'TATA',2500000,'HARRIER','BHUMII');

SELECT BRAND_NAME,PRICE FROM BRANDS

WHERE PRICE BETWEEN 5000 AND 100000;
 PRACTICAL:18

AIM: Write SQL queries using set operators.

There are four major types of SQL operators, namely:

● Union
● Union all
● Intersect
● Minus

SQL Set Operator Function

Union Combines distinct results of two or more

SELECT statements.

Union All Combines all results of two or more SELECT

statements, including duplicates.

Intersect Returns only the common records obtained

from two or more SELECT statements.

Minus Returns only those records which are

exclusive to the first table.

There are certain rules which must be followed to perform operations using SET
operators in SQL. Rules are as follows:

1. UNION:

● UNION will be used to combine the result of two select statements.

● Duplicate rows will be eliminated from the results obtained after

performing the UNION operation.

2. UNION ALL:

● This operator combines all the records from both the queries.

● Duplicate rows will not be eliminated from the results obtained after
performing the UNION ALL operation.

3. INTERSECT:

● It is used to combine two SELECT statements, but it only returns the

records which are common from both SELECT statements.

● The number and order of columns must be the same.

● Data types must be compatible.

CREATE TABLE Emp_Information(

NAME VARCHAR(20),

ADDRESS VARCHAR(15),

DATE DATE(16));
INSERT INTO Emp_Information

VALUES("BHUMI","SURAT","1995-01-22"),

("FENI","VADODARA","1995-01-22"),

("VISHAL","BHARUCH","4356-01-18"),

("GEETA","SURAT","1965-01-6"),

("DINESH","SURAT","1991-01-12"),

("KINJAL","SURAT","1980-01-23");

CREATE TABLE EXPENSE_emp(

INT ID,

NAME VARCHAR(20),

DATE DATE,

PRICE NUMBER,

ADDRESS VARCHAR(15));

INSERT INTO EXPENSE_emp

VALUES(22,'bhumi','22/02/2024',45678,'SURAT'),

(22,'feni','22/02/2024',45678,'VADODARA'),

(22,'kinjal','2/05/2023',45678,'BHARUCH'),
 (22,'vishal','22/09/2022',45678,'SURAT'),

(22,'dinesh','22/07/2021',45678,'SURAT');

1. UNION:

SELECT NAME FROM Emp_Information

UNION

SELECT NAME FROM EXPENSE_emp;

2. UNION ALL:

SELECT NAME FROM Emp_Information

UNION all

SELECT NAME FROM EXPENSE_emp;

3. INTERSECT:

SELECT ADDRESS FROM Emp_Information

INTERSECT

SELECT ADDRESS FROM EXPENSE_emp;

 PRACTICAL:19

AIM: Write SQL queries to retrieve data from multiple tables.

 PRACTICAL:20

AIM: Write SQL queries to show all the records and modify some
data.