Structured Query language
Structured Query language:
SQL is case insensitive.
Always end SQL statements with a semicolon (;).
To enter multiline SQL statements, we don’t write ‘;’ after the first line.
The prompt mysql> then changes to ‘->’, indicating that statement is continued to the
next line. After the last line, put ‘;’ and press enter.
Data type of Attribute - Data type indicates the type of data value that an attribute can
have.
Data Type Description
CHAR(n) Specifies character type data of length n where n could be any value from 0 to
255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve
spaces for 10 characters. If data does not have 10 characters (for example, ‘city’
has four characters
VARCHAR(n) Specifies character type data of length ‘n’ where n could be any value from 0
to 65535. But unlike CHAR, VARCHAR is a variable-length data type.
INT INT specifies an integer value. Each INT value occupies 4 bytes of storage.
The range of values allowed in integer type are -2147483648 to
2147483647.
NUMBER Used to store a numeric value in a column. It may be decimal, integer or real
value.
Syntax: Number(n,d)
Where n specifies the no. of digits and d specifies the no. of digits to the
right of the decimal point.
FLOAT Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
Note: FLOAT( M , D ) or REAL( M , D ) or DOUBLE PRECISION( M , D ) . Here, ( M ,
D ) means than values can be stored with up to M digits in total, of which D
digits may be after the decimal point.
DATE used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digit year, MM is the 2
digit month and DD is the 2 digit date.
Constraints - Constraints are certain types of restrictions on the data values that
an attribute can have. They are used to ensure the accuracy and reliability of data.
DATABASE COMMNADS
1. VIEW EXISTING DATABASE
SHOW DATABASES ;
2. CREATING DATABASE IN MYSQL
CREATE DATABASE ;
3. ACCESSING DATABASE For accessing already existing database
USE Student ;
4. DELETING DATABASE For deleting any existing database
DROP DATABASE
5. VIEWING TABLE IN DATABASE
SHOW TABLES ;
Types of SQL commands: There are five types of SQL commands: DDL, DML, DCL, TCL,
and DQL.
Data Definition Language(DDL)- SQL provides commands for defining the
relation schemas, modifying relation schemas and deleting relations. These are
called Data Definition Language (DDL). Following are the five DDL commands in SQL:
1: CREATE Command :
A Table is a combination of rows and columns. For creating a table we have to define the
structure of a table by adding names to columns and providing data type and size of data to
be stored in columns.
Syntax:
CREATE table table_name ( Column1 datatype (size), column2 datatype (size), . . . . . . .
columnN datatype(size));
Example: Write Create command to Create a table to store data of Customers, so the table
name is Customer, Columns are ID,Name, Country, age, phone, and so on.
Sovle:
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
Country VARCHAR(50),
Age INT,
Phone int(10)
);
2: DROP Command :
The drop command in SQL is used to remove the whole table including data present inside it,
indexes, triggers, constraints, and permission specifications for that particular table. It means
that once the user drops a table, there is no way to undo the command. It can also delete a
whole database or can simply delete a table, it depends on the wish of the user.
Syntax:
DROP TABLE table_name;
3: ALTER Command :
An ALTER TABLE statement can be used to delete, add, or modify the pre-existing columns
in any table. Thus, the ALTER TABLE statement can also be used to add or drop different
constraints on a table that already exists.
ADD – ALTER TABLE : The ADD can be used in order to add columns to an already
existing table.
Syntax : ALTER TABLE name_of_table ADD name_of_column_1 datatype;
DROP – ALTER TABLE : The DROP COLUMN can be used to drop a column in any
given table or in order to delete the unwanted columns present in a table.
Syntax : ALTER TABLE name_of_table DROP COLUMN name_of_column;
ALTER TABLE-MODIFY : The MODIFY can be basically used in order to modify the
pre-existing columns in any given table. One can modify multiple columns at once
using this.
Syntax : ALTER TABLE name_of_table MODIFY name_of_column column_type;
Data ManipulationLanguage(DML)- It means either retrieval (access) of existing
data, insertion of new data, removal of existing data or modification of existing
data in the database.
Insert data into table
Insertion-used to insert new records in a table. Its syntax is:
a) To provide values for all the attributes in the table.
INSERT INTO table_nameVALUES(value 1, value 2,....);
b) To provide values only for some of the attributes in a table
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Data Query
Select Command-The SQL statement SELECT is used to retrieve data from the
tables in a database and the output is also displayed in tabular form.
Syntax:
a) To display all data from the table
SELECT *FROM table_name;
b) To display specific column(s) from the table
SELECT attribute1, attribute2, FROM table_name;
Ques: Write the query for the given questions based on the table:
1: To display the employee number and employee name of all the employees
Ans: SELECT EmpNo, EnameFROM EMPLOYEE;
2: To rename of columns
Ans:SELECT EName AS Name FROM EMPLOYEE;
3: Display names of all employees along with their annual salary
(Salary*12). While displaying query result, rename EName as Name.
Ans:SELECT EName AS Name, Salary*12 FROM EMPLOYEE;
DISTINCT Clause-Used to return records without repetition (distinct records)
Syntax-SELECT DISTINCT DeptIdFROM EMPLOYEE;
WHERE Clause -used to retrieve data that meet some specified conditions.
Syntax-SELECT DISTINCT Salary FROM EMPLOYEE WHERE Deptid='D01';
Example
To display the name and date of birth of student with roll number 2, we
write the following query:
SELECT SName, SDateofBirth FROM STUDENT WHERE RollNumber = 1;
Display all the employees who are earning more than 5000 and work in
department with DeptId D04.
SELECT * FROM EMPLOYEE WHERE Salary > 5000 AND DeptId = 'D04';
To display records of all the employees except Aaliya.
SELECT * FROM EMPLOYEE WHERE NOT Ename = 'Aaliya';
To display name and department number of all those employees who are
earning salary between 20000 and 50000 (both values inclusive).
SELECT Ename, DeptId FROM EMPLOYEE WHERE Salary>=20000 AND
Salary<=50000;
Or
SELECT Ename, DeptId FROM EMPLOYEE WHERE Salary BETWEEN 20000 AND
50000;
MEMBERSHIP OPERATOR IN/NOT IN
The IN operator compares a value with a set of values and returns true if the value
belongs to that set.
Syntax: SELECT * FROM EMPLOYEE WHERE DeptId IN ('D01', 'D02' , 'D04');
Example
The following query displays details of all the employees except those working in
department number D01 or D02.
SELECT * FROM EMPLOYEE WHERE DeptId NOT IN('D01', 'D02');
The following query displays details of all the employees except those working in
department number D01 or D02.
SELECT * FROM EMPLOYEE WHERE DeptId NOT IN('D01', 'D02');
To display details of all the employees who are working either in DeptId
D01, D02 or D04.
SELECT * FROM EMPLOYEE WHERE DeptId= 'D01' OR DeptId = 'D02' OR DeptId =
'D04';
Or
SELECT * FROM EMPLOYEE WHERE DeptId IN ('D01', 'D02' , 'D04');
Handling NULL Values - NULL to represent a missing or unknown value.
Example
To display details of all those employees who have not been given a bonus.
This implies that the bonus column will be blank.
SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
To display names of all the employees who have been given a bonus. This
implies that the bonus column will not be blank.
SELECT EName FROM EMPLOYEE WHERE Bonus IS NOT NULL;
Pattern Matching: SQL provides LIKE operator that can be used with WHERE
clause to search for a specified pattern in a column.
The LIKE operator makes use of the following two wild card characters:
a) % (percent)— used to represent zero, one, or multiple characters
b) _ (underscore)— used to represent a single character
Example
To display details of all those employees whose name starts with 'K'.
SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';
To display details of all those employees whose name ends with 'a'.
SELECT * FROM EMPLOYEE WHERE Ename LIKE '%a';
To display details of all those employees whose name consists of exactly 5
letters and starts with any letter but has ‘ANYA’ after that.
SELECT * FROM EMPLOYEE WHERE Ename LIKE '_ANYA';
To display names of all the employees containing 'se' as a substring in name.
SELECT Ename FROM EMPLOYEE WHERE Ename LIKE '%se%';
To display names of all employees containing 'a' as the second character.
SELECT EName FROM EMPLOYEE WHERE Ename LIKE '_a%';
Updation - used to make such modifications in the existing data.
Syntax: UPDATE table_name SET attribute1 = value1, attribute2 = value2, ...
Deletion- The DELETE statement is used to delete one or more record(s) from a
table.
Syntax: DELETE FROM table_name WHERE condition;
ORDER BY clause – It is used to display the result of an SQL query in ascending or
descending order with respect to specified attribute values. The default is ascending
order.
Example
To display details of all the employees in descending order of their salaries.
SELECT * FROM EMPLOYEE ORDER BY Salary DESC;
