SQL

Structured Query Language

Lets do practical on DATABASE…

SQL – Structured Query Language

• Is a language that enables you to create and operate on relational databases
• It is the standard language used by almost all the database s/w vendors.
• Original version was developed by IBM’s Almanden Research Center
• Latest ISO standard of SQL was released in 2008 and named as SQL:2008

SQL – features

• Allows creating/modifying a database’s structure
• Changing security settings for system
• Permitting users for working on databases or tables
• Querying database
• Inserting/modifying/deleting the database contents

MYSQL Elements

• Literals
• Data types
• Nulls
• Comments

Literals

• It means the fixed value or constant value. It may be of character, numeric or date time type.
• Character and date/time literals are always in single quotation marks whereas numeric literals must be without single quotation marks
• For example – ‘Virat’, 12, 12.56, ‘04-20-2018’

format

• Date and time values are always in the YYYY-MM-DD HH:MI:SS

• Special character like quotes are always written be preceding it back-slash(\). For example if we want to store value as Tom’s Cat then it should be written as Tom\’s Cat

Data Type

• Means the type of value and type of operation we can perform on data. For example on numeric value we can store numbers and perform all arithmetic operations and so on.
• MySQL support three categories of data types:
• Numeric
• Date and time
• String types

Numeric Data Types

Date and Time Types

String Types

Difference between CHAR & VARCHAR

NULL VALUE

• NULL means missing information
• NULL can appear in any type of column if it is not restricted by

NOT NULL or PRIMARY KEY

• Always remember NULL is neither equal to 0 nor space. NULL means nothing
• Used in situation like if email id is not available with students

then we will insert NULL

COMMENTS

• It is a text that is not executed, only for documentation purpose. Comments in MySQL can be written as
• Begin the comment with /* and */
• For example
• /* Select * from emp where empno=4 */
• Select * from emp; -- it will fetch all details

SQL COMMAND SYNTAX

CREATING and USING DATABASE

CREATE DATABASE <DATABASE NAME>

CREATE DATABASE MYDB;

​

TO SEE LIST OF DATABASES: SHOW DATABASES;

​

TO OPEN ANY DATABASE TO WORK

USE DATABASENAME

USE MYDB;

CREATING TABLE

datatype(size),

Syntax:-

Create Table TableName(ColumnName ColumnName datatype(size),…..);

Example:-

Create Table Employee(empno int primary key , name varchar(20) not null, dept varchar(20) not null, salary int not null);

​

Create table Student(roll int primary key , name varchar(20) not null ,stream varchar(20) not null, per int not null);

INSERTING RECORDS IN TABLE

Syntax:-

Insert into tablename values(value1,value2,…)

​

Note:-

1. char, varchar and date value must be in single quotes.
2. Values must be passed in the order of their column.
3. Date values are passed in the format

​

yyyy-mm-dd (in mysql)

INSERTING RECORDS IN TABLE

Syntax:-

Insert into emp values(1, ‘Rakesh’,’Sales’,34000) Insert into student values(1,’Mahi’,’Science’,89); Inserting in selected columns

Insert into emp (empno, name, dept ) values (2,’dipanker’,’IT’);

• A constraints refers to condition or limitation we apply on any column so that only correct information will be entered in table.

​

• MySQL allows to apply constraint by two methods
• At the time of table creation
• After table creation

• PRIMARY KEY : ensures unique value in any column also forces data entry mandatory. Only one primary key can be applied in one table

​

• UNIQUE : also allows unique value in any column but it allows NULL values and can be applied to n times

​

• NOT NULL : it will make data entry mandatory for applied column i.e. NULL will not be allowed

​

• DEFAULT : it allows to specify any value which will be automatically inserted in applied column if we not specify applied column at the time of data entry using INSERT

• CHECK : allows to specify range of values that can be entered in applied column like salary must be greater than 2000, marks must be greater than 0 or dept must be in given list of values etc.

​

• FOREIGN KEY: allows to establish relationship between

2 tables. Foreign key column will be dependent on PRIMARY KEY column of another table and allows to enter only those values in foreign key whose corresponding value exists in PRIMARY KEY

Now lets check PRIMARY KEY is working or not by inserting

duplicate empno

​

​

​

​

Now lets check NOT NULL is working or not by inserting

NULL value in name column

Now let us check how DEFAULT constraint to use. (Remember to use DEFAULT CONSTRAINT, The applied column name will not be used with INSERT

Default value ‘Marketing’ is automatically inserted

• MySQL allows us to get the structure of table like list of columns, data type, size and key information of table using DESC / DESCRIBE command
• Example

SELECTING RECORD

Select statement allows to send queries to table and fetch the desired record. Select can be used to select both horizontal and vertical subset.

​

Syntax:-

Select <columnnames> FROM tablename [ where condition ];

SELECTING RECORD

Selecting all record and all columns

select * from emp;

​

Selecting desired columns

select empno, name from emp;

​

Changing the order of columns

select dept, name from emp;

DISTINCT keyword

DISTINCT keyword is used to eliminate the duplicate records from output. For e.g. if we select dept from employee table it will display all the department from the table including duplicate rows.

​

select dept from emp;

​

Output will be:-

​

Dept

--------

Sales Sales IT

IT

HR

DISTINCT keyword

If we don’t want to see the duplicate rows in output we have to use DISTINCT keyword.

​

Select DISTINCT dept from emp;

​

Output will be:-

​

Dept

--------

Sales IT HR

PERFORMING SIMPLE CALCULATION

While performing SQL operations sometimes simple calculations are required, SQL provides facility to perform simple arithmetic operations in query. In MySQL we can give these queries without FROM clause i.e. table name is not required for these queries,

For Example

select 10*2; select 10*3/6;

PERFORMING SIMPLE CALCULATION

MySQL also provides DUAL table to provide compatibility with other DBMS. It is dummy table used for these type queries where table name is not required. It contains one row and one column. For example:

select 100+200 from DUAL; select curdate() from dual; CALCULATION WITH TABLE DATA

Select name, salary, salary * 12 Annual_Salary from emp; Select empno, salary+1000 from emp

Update student set total=phy+chem+maths+cs+eng;

COLUMN ALIAS

It is a temporary name/label given to column that will appear in output. For example if column name is dept and you want Department to appear as column heading then we have to give Column Alias. If we want alias name of multiple words then it should be enclosed in double quotes. Its format is :

ColumnName [AS] ColumnAlias

Example

1. select empno Employee_Number, name, dept Department, Salary Income from emp;
2. select name, Salary*12 as “Annual Income” from emp;

HANDLING NULL

From the above table we can observe that salary of Shaban is NULL i.e. not assigned, Now if we want 0 or “not assigned” for the salary information of shaban, we have to use IFNULL()

select empno,name,IFNULL(Salary,”not assigned”) from emp;

​

Column value to substitute if NULL found

PUTTING TEXT IN QUERY OUTPUT

SQL allows to put user defined symbols or text with table output. Like ‘Rs’ with Salary or ‘%’ symbol with commission

For e.g.

​

select name, dept, ‘Rs.’, salary from emp;

​

select name, ‘ works in department’, dept, ‘ and getting salary rs. ‘, salary from emp;

​

select name, concat(‘Rs. ‘, salary) from emp;

WHERE clause

WHERE clause is used to select specified rows. It allows to select only desired rows by applying condition. We can use all comparison(>, <, >=, <=, =, <>) and logical operator (AND, OR, NOT).

AND ( &&), OR (||) , NOT (!)

For example

select * from emp where salary>4000; select * from emp where empno=1;

select name,dept from emp where dept=‘HR’;

WHERE clause

AND(&&) means both conditions must be true, OR(||) means any condition must be true to produce output. NOT(!) will do the reverse checking.

​

select * from emp where salary>4000 and salary<8000; select * from emp where dept=‘Sales’ and salary<30000;

select name,dept from emp where dept=‘HR’ and salary>=20000 and salary<=40000;

select * from emp where dept=‘HR’ or dept=‘IT’; select * from emp where NOT empno=4;

SQL operators

1. BETWEEN
2. IN
3. LIKE
4. IS NULL

BETWEEN

BETWEEN allows to specify range of values to search in any column. It is used with AND clause and it will include the specified values during the searching. For e.g.

select * from emp where salary between 18000 and 30000; select name from emp where empno between 2 and 5;

​

select * from emp where salary NOT between 25000 and 35000

IN

IN allows to specify LIST of values in which searching will be performed. It will return all those record that matches any value in a given list of values. It can be thought as an alternative of multiple ORs

select * from emp where dept IN(‘sales’,’it’); select name from emp where empno IN (2,4,5); select * from emp where dept NOT IN(‘sales’,’it’)

LIKE

LIKE allows to search based on pattern. It is used when we don’t want to search an exact value or we don’t know that exact value, and we know only the pattern of value like name starting from any particular letter, or ending with and containing any particular letter or word.

​

LIKE is used with two wildcard characters:

1. % : used when we want to substitute multiple characters. With % length is not fixed
2. _ (underscore) : used when we want to substitute Single character

LIKE

Search for employee whose name begins from ‘s’

select * from emp where name like ‘s%’;

​

Search for employee whose name ends with ‘r’

​

select * from emp where name like ‘%r’;

​

Search for employee whose name contains ‘a’ anywhere

​

select * from emp where name like ‘%a%’

​

Search for employee whose dob is in feb

​

select * from emp where dob like ‘%-02-%’

​

Search employee whose name is of 5 letters begins from ‘s’

​

select * from emp where name like ‘s_ _ _ _ ’;

IS NULL

IS NULL is used to compare NULL values present in any column. Because NULL is not considered as value so we cannot compare with = sign, so to compare with NULL SQL provides IS NULL.

select * from emp where salary is null; select * from emp where salary is not null;

OPERATOR PRECEDENCE

When multiple operators are used in expression, then evaluation of expression takes place in the order of precedence. Higher precedence operator will execute

first.

HIGH

LOW

SORTING OUTPUT

By default records will come in the output in the same order in which it was entered. To see the output rows in sorted or arranged in ascending or descending order SQL provide ORDER BY clause. By default output will be ascending order(ASC) to see output in descending order we use DESC clause with ORDER BY.

​

select * from emp order by name; (ascending order) select * from emp order by salary desc;

select * from emp order by dept asc, salary desc;

MYSQL FUNCTIONS

A function is built – in code for specific purpose that takes value and returns a single value. Values passed to functions are known as arguments/parameters.

There are various categories of function in MySQL:-

1. String Function
2. Mathematical function
3. Date and time function

String Function

String Function

Numeric Function

Date and Time Function

Date and Time Function

Difference Between NOW() and SYSDATE() :

NOW() function return the date and time at which function was executed even if we execute multiple NOW() function with select. whereas SYSDATE() will always return date and time at which each SYDATE() function started execution. For example.

mysql> Select now(), sleep(2), now();

Output: 2018-12-04 10:26:20, 0, 2018-12-04 10:26:20

mysql> Select sysdate(), sleep(2), sysdate();

Output: 2018-12-04 10:27:08, 0, 2018-12-04 10:27:10

AGGREGATE functions / GROUP functions�

Aggregate function is used to perform calculation on group of

rows and return the calculated summary like sum of salary,

average of salary etc.

Available aggregate functions are –

1. SUM()
2. AVG()
3. COUNT()
4. MAX()
5. MIN()
6. COUNT(*)

AGGREGATE functions

select SUM(salary) from emp;

Output – 161000

select SUM(salary) from emp where dept=‘sales’;

Output - 59000

AGGREGATE functions

select AVG(salary) from emp;

Output – 32200

select AVG(salary) from emp where dept=‘sales’;

Output - 29500

AGGREGATE functions

select COUNT(name) from emp;

Output – 5

select COUNT(salary) from emp where dept=‘HR’;

Output - 1

select COUNT(DISTINCT dept) from emp;

Output - 3

AGGREGATE functions

select MAX(Salary) from emp;

Output – 45000

select MAX(salary) from emp where dept=‘Sales’;

Output - 35000

AGGREGATE functions

select MIN(Salary) from emp;

Output – 24000

select MIN(salary) from emp where dept=‘IT’;

Output - 27000

AGGREGATE functions

select COUNT(*) from emp;

Output – 6

select COUNT(salary) from emp;

Output - 5

count(*) Vs count()

Count(*) function is used to count the number of rows in query output whereas count() is used to count values present in any column excluding NULL values.

Note:

All aggregate function ignores the NULL values.

GROUP BY

GROUP BY clause is used to divide the table into logical groups and we can perform aggregate functions in those groups. In this case aggregate function will return output for each group. For example if we want sum of salary of each department we have to divide table records

Aggregate functions by default takes the entire table as a single group that’s why we are getting the sum(), avg(), etc output for the entire table. Now suppose organization wants the sum() of all the job separately, or wants to find the average salary of every job. In this case we have to logically divide our table into groups based on job, so that every group will be passed to aggregate function for calculation and aggregate function will return the result for every group.

aggregate

column value. In those logically divided records we can apply functions.

For. E.g.

SELECT SUM(SAL) FROM EMP GROUP BY DEPT; SELECT JOB,SUM(SAL) FROM EMP GROUP BY DEPT; SELECT JOB,SUM(SAL),AVG(SAL),MAX(SAL),COUNT(*) EMPLOYEE_COUNT FROM EMP;

​

NOTE :- when we are using GROUP BY we can use only aggregate function and the column on which we are grouping in the SELECT list because they will form a group other than any column will gives you an error because they will be not the part of the group.

For e.g.

SELECT ENAME,JOB,SUM(SAL) FROM EMP GROUP BY JOB;

Error -> because Ename is not a group expression

HAVING with GROUP BY

• If we want to filter or restrict some rows from the output produced by GROUP BY then we use HAVING clause. It is used to put condition of group of rows. With having clause we can use aggregate functions also.
• WHERE is used before the GROUP BY. With WHERE we cannot use aggregate function.
• E.g.
• SELECT DEPT,AVG(SAL) FROM EMP GROUP BY DEPT HAVING JOB IN (‘HR’,’SALES’)

​

• SELECT DEPT,MAX(SAL),MIN(SAL),COUNT(*) FROM EMP GROUP BY DEPT HAVING COUNT(*)>2

​

• SELECT DEPT,MAX(SAL),MIN(SAL) FROM EMP WHERE SAL>=2000 GROUP BY DEPT HAVING DEPT IN(‘IT’,’HR’)

JUST A MINUTE…

• Create the following table and add the records

• ALTER TABLE command allows us to perform the following operations:
• Adding new column in existing table
• Dropping existing column from table
• Modifying column definition in table
• Changing the name of column
• Adding or dropping constraint after table creation.

After new column is added, if you select record it will display NULL in that column for previous record, we have to update it using UPDATE command

ALTER TABLE ABCLtd drop designation;

Dropping primary key

ALTER TABLE EMP DROP PRIMARY KEY CASCADE;

While dropping Primary Key, if it is connected with child table, it will not gets deleted By default, however if you want to drop it we have to issue following commands

• DROP TABLE[IF EXISTS] tablename
• Example
• Drop Table emp;
• Drop table if exists emp;

JUST A MINUTE…

Write down the following queries based on the given table:

1. Select all record of table
2. Select ItemNo, name and Unitprice
3. Select all item record where Unitprice is more than 20
4. Select Item name of those items which are quantity between 100-200
5. Select all record of Items which contains pen word in it
6. Select unique dcode of all items
7. Display all record in the descending order of UnitPrice
8. Display all items which are stocked in the month of March

JUST A MINUTE…

Write down the following queries based on the given table:

9) Change the unitprice to 20 for itemno 5005

10) Delete the record of itemno 5001

11. Display all the item name in capital letters
12. Display first 4 character of every item name
13. Display all record whose dcode is not assigned