SHORT Notes My SQL

Functions in SQL

Functions can be applied to work on single or multiple records (rows) of a table.

Single Row Functions

These are also known as Scalar functions. Single row functions are applied on a single value and return a
single value.

single row functions under three categories — Numeric (Math), String, Date and Time.

Numeric Function : POWER(), ROUND(), MOD()

String Function : UCASE() LCASE() MID() LENGTH() LEFT() RIGHT() INSTR() LTRIM()
RTRIM() TRIM()

Date Function : NOW(), DATE(), MONTH(), MONTHNAME() ,YEAR(), DAY(), DAYNAME()

mysql> SELECT POWER(2,3); Output: 8

mysql>SELECT ROUND(2912.564, 1); Output: 2912.6 mysql> SELECT ROUND(283.2); Output: 283
select round(342.9234, -1) ; Output 340
SELECT ROUND(543.5694,2),ROUND(543.5694),ROUND(543.5694,-1);
Ans is : 543.57 , 544 540
SELECT ROUND(546.3694,-1); The Ans is : 550

SELECT MOD(21, 2); Output: 1

Calculate GST as 12% of Price and display the result after rounding it off to one decimal place. mysql>
SELECT ROUND(12/100*Price,1) "GST" FROM INVENTORY;

Calculate and display the amount to be paid each month (in multiples of 1000) which is to be calculated after
dividing the FinalPrice of the car into 10 instalments.

d) After dividing the amount into EMIs, find out the remaining amount to be paid immediately, by
performing modular division. Following SQL query can be used to solve the above mentioned problems:

select CarId, FinalPrice, ROUND((FinalPriceMOD(FinalPrice,10000))/10,0) "EMI", MOD(FinalPrice,10000)

"Remaining Amount" FROM INVENTORY;

Display InvoiceNo, SalePrice and Commission such that commission value is rounded off to 0.
mysql> SELECT InvoiceNo, SalePrice, Round(Commission,0) FROM SALE;

SELECT UCASE(“Informatics Practices”);Output:INFORMATICS PRACTICES

SELECT LOWER(“Informatics Practices”);Output:informatics practices
SELECT MID(“Informatics”, 3, 4); Output: form
MID/SUBSTR/SUBSTRING Have Same syntax and purpose

SELECT LENGTH(“Informatics”);Output: 11
SELECT LEFT(“Computer”, 4); Output:Comp
SELECT RIGHT(“SCIENCE”, 3);Output: NCE
mysql> SELECT INSTR(“Informatics”, “ma”);Output: 6
mysql> SELECT LENGTH(“ DELHI”), LENGTH(LTRIM(“ DELHI”));Output: | 7 | 5 |
Display the length of the email and part of the email from the email ID before the character ‘@’. Note - Do
not print ‘@’.
mysql> SELECT LENGTH(Email), LEFT(Email, INSTR(Email, "@")-1) FROM CUSTOMER;

SELECT NOW(); Output:2019-07-11 19:41:17

SELECT DATE(NOW()); Output:2019-07-11
SELECT MONTH(NOW()); Output:7
SELECT MONTHNAME(“2003-11-28”); Output:November
SELECT YEAR(“2003-10-03”); Output:2003
SELECT DAYNAME(“2019-07-11”); Output:Thursday
SELECT YEAR(CURDATE()),MONTH(CURDATE()), DAY(CURDATE());
2025 2 23

Aggregate Functions in SQL

MAX(),MIN(),SUM(), COUNT(),AVG()

SELECT COUNT(*) from MANAGER; Ans : 4

GROUP BY in SQL
At times we need to fetch a group of rows on the basis of common values in a column. This can be done using
a GROUP BY clause.
We can use the aggregate functions (COUNT, MAX, MIN, AVG and SUM) to work on the grouped values.
HAVING Clause in SQL is used to specify conditions on the rows with GROUP BY clause.
Example
Display the number of cars purchased by each customer from the SALE table.
mysql> SELECT CustID, COUNT(*) "Number of Cars" FROM SALE GROUP BY CustID;

Display the customer Id and number of cars purchased if the customer purchased more than 1 car from SALE
table.
mysql> SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID HAVING Count(*)>1;

Display the number of people in each category of payment mode from the table SALE.
mysql> SELECT PaymentMode, COUNT(PaymentMode) FROM SALE GROUP BY Paymentmode ORDER
BY Paymentmode;

Display the PaymentMode and number of payments made using that mode more than once.
mysql> SELECT PaymentMode, Count(PaymentMode) FROM SALE GROUP BY Paymentmode HAVING
COUNT(*)>1 ORDER

Multiple Table Example

Create Table department(

ID int,
SALARY int,
NAME Varchar(20),
DEPT_ID Varchar(255));

CREATE TABLE employee(

ID int,
Email Varchar(255),
City Varchar(20) );
Query:
SELECT [Link], [Link], [Link], [Link]
FROM department, employee
WHERE [Link] = [Link];

Some Questions
What is the purpose of the following clauses in a
select statement?
i) ORDER BY
ii) HAVING
Q Site any two differences between Single_row
functions and Aggregate functions.

Write the name of the functions to perform the

following operations:
i) To display the day like “Monday”, “Tuesday”,
from the date when India got independence.
ii) To display the specified number of characters from a particular position of the given string.
iii) To display the name of the month in which you were born.
iv) To display your name in capital letters.

Consider the following table named “Product”,

showing details of products being sold in a grocery
shop.
PCOD UPRIC
PNAME MANUFACTURER
E E
Washing Powder Surf
P01 120
P02 Tooth Paste 54 Colgate
P03 Soap 25 Lux
P04 Tooth Paste 65 Pepsodant
P05 Soap 38 Dove
P06 Shampoo 245 Dove
a) Write SQL queries for the following:
i. Create the table Product with appropriatedata types and constraints.
ii. Identify the primary key in Product.
[Link] the Product Code, Product name and price in descending order of their product name. If PName is the
same then display the data in ascending order of price.
iv. Add a new column Discount to the table Product.
v. Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products
where the UPrice is more than 100, otherwise the discount will be 0.
vi. Increase the price by 12 per cent for all the products manufactured by Dove.
[Link] the total number of products manufactured by each manufacturer.
Possible Answer

1) Create Table product (Pcode Char(10), PNAME CHAR(30), UPRICE QTY

NUMERIC(5),MANUFACTURER CHAR(35));

II) PCODE

III) SELECT PCODE,PNAME,UPRICE FROM PRODUCT ORDER BY PNAME DESC;

IV) ALTER TABLE PRODUCT ADD COLUMN DISCOUNT NUMERIC(8,2);

V) UPDATE PRODUCT SET DISCOUNT = UPRICE*10/100 WHERE UPRICE >100;

VI) UPDATE PRODUCT SET UPRICE= UPRICE+UPRICE*12/100 WHERE
MANUFACTURER=”DOVE”;

VII) SELECT COUNT(PNAME) FROM PRODUCT GROUP BY MANUFACTURER;

DDL STATEMENTS IN MY SQL

CREATE: Example:
CREATE TABLE (table name)

ALTER TABLE ( Table Name) ADD/DROP/MODIFY DROP TABLE (table name)

DML STATEMENTS

INSERT INTO (table name) ,

SELECT * From …..,

UPDATE (Table Name) SET …..,

DELETE FROM (table name)

DIFFERENCE BETWEEN DROP & DELETE

Drop : It Deletes data including table .

Delete : It deletes Data Only .

Difference between GROUP BY and ORDER BY

Order BY : This Clause arrange the records either Ascending or Descending Order.

GROUP BY : The GROUP BY function in SQL organizes identical data into groups, enabling aggregate
analysis on each group. It is commonly used with aggregate FUNCTIONS .

Difference between HAVING and Where Clause

The main difference between WHERE and HAVING clause is that the WHERE clause allows you to
select or filter data from specific rows (individual rows) from a table based on certain conditions. In
contrast, the HAVING clause allows you to filter data from a group of rows in a query based on
conditions involving aggregate values.

Having clause applied with GROUP BY only

Distinct Clause : It used to retrieve unique values( Non duplicate) from table .

Ex : Select Distinct( State) from student;

Examples :

Tables : Mobilemaster

Mid m_company M_Name M_price m_dt

MB001 Samsung Galaxy 4500.50 2013-02-12

MB003 NOKIA NIL00 22500.78 2011-04-15

MB004 MICROMAX UNITED 4500.00 2016-10-17

MB005 SONY XPERIA 7500.98 2017-11-20

MB006 OPPO SELFIEX 8500.76 2010-08-21

Table : MobileStock

S_ID Mid m_qty M_suppl

S001 MB004 450 NEW VISION

S002 MB003 250 PRAVEEN GALLERY

S003 MB001 300 CLASSIC MOBILE STORE

S004 MB006 150 A ONE MOBILE

S005 MB003 150 THE MOBILE

S006 MB006 NULL MOBILE CENTER

Q) Display Moile company , name in descending order of their manuf. Date

Ans : SELECT M_COMPANY, M_NAME FROM MOBILEMASTER ORDER BY M_DT;

Q)List Detail of mobile whose name start with “S” or ends with “a”;

Ans: Select *from MobileMaster where m_Name like “S%” or m_Name LIKE “%a”;

Q) Display Mobile Supplier & Quantity of all mobile except “MB003”

Ans : Select m_supp, m_qty from MobileStock where m_id <>”MB003”;

Also where m_id NOT IN(“MB003”)

Q) List the name of mobile company having price between 3000 & 5000.

Ans : Select m_company from Mobile Master where m_price BETWEEN 3000 and 5000.

Q) Display m_id and sum of Mobile Quantity in each M_id;

Ans : SELECT M_ID, SUM(M_QTY) FROM MOBILESTOCK group by m_id;

Q Find Average Price ?

Ans : SELECT AVG(M-Price) from MobileMaster;

Q Display number of records for each M_id ;

Ans : SELECT COUNT(*) FROM MOBILESTOCK GROUP BY M_ID

Q) Increase Price of Mobile By 10 %

Ans : UPDATE MOBILEMASTER SET M_PRICE=M_PRICE+M_PRICE*10/100 ;

Q Display number of records along with sum of m_qty for each individual m_id where number of
records are more than 1.

Ans SELECT M_ID ,COUNT(*) ,SUM(M_QTY)FROM MOBILESTOCK GROUP BY M_ID

HAVING COUNT(*)>1;

Q. Display Details from mobilestock of no stock of mobile.

Ans : SELECT *FROM MOBILESTOCK WHERE M_QTY IS NULL;

Write output of the following statement

Table : salesman

SNO SNAME SALARY BONUS DOJ

A01 BEENA MEHTA 30000 45.23 29/10/2019

A02 KL SAHAV 50000 25.34 13/03/2018

B03 NISHA THAKKAR 30000 35.00 18/03/2017

B04 LEELA YADAV 80000 NULL 18/03/2017

C05 GAUTAM 20000 NULL 21/12/2018

Q1. SELECT SNAME,ROUND(BONUS,0) FROM SALESMAN;

Ans
SNAME BONUS
BEENA MEHTA 45
KL SAHAV 25
NISHA THAKKAR 35
LEELA YADAV NULL
GAUTAM NULL
Q2. SELECT MONTHNAME(DOJ) FROM SALESMAN;
Ans:
DOJ
OCTOBER
MARCH
MARCH
MARCH
DECEMBER
Q3. SELECT MIN(SALARY) FRO SALESMAN;
Ans 20000

Q4. SELECT MOD(SALARY,4000) FROM SALESMAN WHERE SNAME=”KL SAHAV”;

ANS : 2000 (The Remainder Value)