SQL

Class 12th Informatic Practices

Database Concept
● A database is a collection of data that is organized so that it can be easily accessed,
managed and updated.
● Example : school Database, website database, company database etc.
● Need of Database :
1. To manage large chunks of data
2. Accuracy
3. Ease of updating data
4. Security of data
5. Data integrity
● DBMS (Database Management System)
● A DBMS refers to a software that is responsible for storing, maintaining and
utilizing a database in an efficient way.
● Examples : Oracle, MS SQL Server, MS Access, Paradox, DB2 and MySQL etc.
● Advantages of DBMS :
1. Reduces Data Redundancy
2. Ensures Data Integrity
3. Enhances Data Security
4. Facilitates Data Sharing
5. Provides Backup and Recovery

Relation database
● A relational database is a collective set of multiple data sets organized by tables
(rows/columns)
● A Relational database uses Structured Query Language (SQL), which is a standard
user application that provides an easy programming interface for database
interaction.
● Terminology
1. Relation (Table) A Relation or Table is a Matrix -like structure arranged in Rows
and Columns.
2. Domain : :It is a collection of values from which the value is derived for a
column.
3. Rows : Tuples/Entity/Record
4. Columns : Attribute/Field
5. Degree : No. of columns
6. Cardinality : No. of rows

MySQL
● MySQL is an open-source relational database management system (RDBMS) that uses
Structured Query Language (SQL) for managing and interacting with databases.
● It allows users to store, retrieve, and manipulate data efficiently and is widely used in
web applications, data management, and analytics. MySQL is known for its speed,
reliability, and ease of use
● MySQL Features :
1. Open Source & Free of Cost: It is Open Source and available at free of cost.
2. Portability: Small enough in size to install and run it on any type of Hardware
and OS like Linux,MS Windows or Mac etc.
3. Security : Its Databases are secured & protected with password.
4. Connectivity : Various APIs are developed to connect it with many programming
languages.
5. Query Language It supports SQL (Structured Query Language) for handling
databases.

SQL
● SQL (Structured Query Language) is a standardized programming language used for
managing and manipulating relational databases.
● It allows users to perform tasks such as querying data, updating records, inserting
new data, and deleting data within a database.
● SQL is essential for database management systems like MySQL, PostgreSQL, and
Oracle, enabling efficient data handling through
● Advantages of SQL :
1. High speed.
2. No coding needed.
3. Well defined standards.
4. Portability.
5. Interactive language.
6. Multiple data views

SQL keys
● SQL Key plays an important role in relational databases; it is used for identifying
unique rows from tables & establishing relationships among tables on need.
● Types of keys in DBMS
1. Primary Key – A primary is a column or set of columns in a table that uniquely
identifies tuples (rows) in that table.
2. Candidate Key –It is an attribute or a set of attributes or keys participating for
Primary Key, to uniquely identify each record in that table.
3. Alternate Key – Out of all candidate keys, only one gets selected as primary key,
remaining keys are known as alternate or secondary keys.
4. Foreign Key – Foreign keys are the columns of a table that points to the primary
key of another table. They act as a cross-reference between tables.

SQL Data Types

● Numeric
1. Int : positive, negative, zero
2. Float : decimal number (78.90, 23.45)
● String
1. Chart(n) : Fixed Length (Range : 0 to 255)
2. Varchar(n) : Variable length (Range : 0 to 255)
n = no. of character
● Date : Date-Format: YYYY-MM-DD ,Example: 2006-09-23

Types of SQL Language

1. Data Definition Language ( DDL ) : To create database and table structure
CREATE, DROP, ALTER
2. Data Manipulation Language ( DML ) : Rows related operations and manage data
INSERT, DELETE, UPDATE
3. Data Query Language ( DQL ) : To retrieve the data from database
SELECT
Basic Commands
● Show database ; → display all databases in system
● Show tables ; → display all tables in database
● use TABLE_Name ; → select particular database
● desc TABLE_Name ; → describe the table structure
● Select * From TABLE_Name ; → display table with all columns

SQL Commands
1. CREATE
● CREATE DATABASE databasename ; → create database
● Create table table_name( column1_name datatype, column2_name datatype,
column3_name datatype, column4_name datatype ) ; → create table
2. ALTER : Change in Columns
● ALTER TABLE table_name ADD column_name datatype; → add column
● ALTER TABLE table_name DROP COLUMN column_name; → delete column
● ALTER TABLE table_name MODIFY COLUMN column_name datatype; → modify
column
3. DROP
● DROP DATABASE databasename; → delete database
● DROP TABLE table_name; → delete table
4. INSERT
● INSERT INTO table_name VALUES (value1, value2, value3); → add row
● INSERT INTO table_name (column1, column2, column3) VALUES (value1,
value2, value3); → add specific values in row
5. DELETE
● DELETE FROM table_name WHERE condition; → delete column
6. UPDATE
● UPDATE table_name SET column = value WHERE condition; → update row
7. SELECT
● SELECT * FROM table_name ; → display table with all column
● SELECT column1, column2 FROM table_name; → display table with specific
column
Creating Table using SQL command

Database : School
Table : Student

SQL Command
→ create database school ;
→ use school ;
→ create table student (id int, name varchar(20), age int, marks int);
→ insert into student values (1, “Alice”, 16, 85);
→ insert into student values (2, “Bob”, 17, 90);
→ insert into student values (3, “Charlie”, 16, 78);
→ Select * From Student;

Operators
1. Mathematical : (+, -, *, /, %)
● Example : Select 12 + 56 ; → 68
2. Relational : (=, >, <, >=, <=, !=)
● Example : Select 12 > 56 ; → 0
3. Logical : (AND, OR, NOT)
● True : 1 and False : 0
● Example : Select (4>2) and (56 < 45) ; → 0
4. In
● SELECT column_name FROM table_name WHERE column_name IN (value1,
value2);
● Example : SELECT mark FROM student WHERE marks in (45, 67, 89, 90);
5. Between
● SELECT column_name FROM table_name WHERE column_name BETWEEN
value1 AND value2;
● Example : SELECT mark FROM student WHERE mark between 60 and 70 ;
6. Like
● SELECT column_name FROM table_name WHERE column LIKE pattern;
● Example : SELECT name from student where name like “%R” ;
● Pattern :
1. Start : T%
2. Mid : %T%
3. End : %T

Clause
● A clause is a component of a query that specifies certain conditions or actions. Each
clause serves a specific purpose in a SQL statement.
Types of Clause :
1. WHERE : display data on specific condition
● SELECT * FROM table_name WHERE condition ;
● Example : SELECT * FROM student WHERE mark >= 50 ;
2. DISTINCT : display unique values
● SELECT DISTINCT Column_name FROM table_name ;
● Example : SELECT DISTINCT name FROM student ;
3. ALIAS
● SELECT column_name as alternative_column_name FROM table_name ;
● Example : SELECT name as Student Name FROM Student ;
4. ORDER By
● SELECT * FROM table_name ORDER BY column_name ASC ; → Ascending order
● SELECT * FROM table_name ORDER BY column_name DESC ; → Descending order
● Example : SELECT * FROM student ORDER BY name ASC ;

Group By Having
● The GROUP BY clause in SQL is used to combine rows that have the same values of a
particular column.
● Syntax : SELECT column_name , AggregateFunctions( column_name ) FROM
table_name GROUP BY column_name ;
● Example
→ SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY
department;

Having
● The HAVING clause in SQL is used to filter records after they have been grouped by
the GROUP BY clause.
● Syntax : SELECT column_name , AggregateFunctions( column_name ) FROM
table_name where condition GROUP BY column_name having condition ;
● Example :

→ SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY

department HAVING SUM(salary) > 100000;
Join
● A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
● Types of Join :
1. (INNER) JOIN
2. LEFT (OUTER) JOIN
3. RIGHT (OUTER) JOIN
4. FULL (OUTER) JOIN
5. Equi Join
6. Natural Join
● Equi Join
❖ EQUI JOIN creates a JOIN for equality or matching column(s) values of the
relative tables.
❖ EQUI JOIN also creates JOIN by using JOIN with ON and then providing the
names of the columns with their relative tables to check equality using equal
sign (=).
❖ Syntax :
1. SELECT table1.column, table2. column FROM table1, table2 WHERE
table1.column_name =table2.column_name;
2. SELECT table1.column, table2. column FROM table1 JOIN table2 ON
join_condition;
❖ Example :
→ SELECT students.student_id, students.name, students.age, marks.marks
FROM students JOIN marks ON students.student_id = marks.student_id;

Aggregate Functions
1. MAX(): The MAX() function returns the maximum value from a column or a set of
values.
2. MIN(): The MIN() function returns the minimum value from a column or a set of
values.
3. AVG(): The AVG() function returns the average value from a column or a set of values
4. SUM(): The SUM() function returns the total sum of a column or a set of values.
5. COUNT(): The COUNT() function returns the number of rows that match a specified
condition.

Math Functions
1. POWER()
● The POWER() function raises a number to a specified power.
● For example, POWER(2, 3) returns the result of 2 raised to the power of 3,
which is 8.
2. ROUND()
● The ROUND() function rounds a number to a specified number of decimal
places.
● For instance, ROUND(3.14159, 2) rounds the number to 2 decimal places,
resulting in 3.14.
3. MOD()
● The MOD() function returns the remainder when one number is divided by
another.
● For example, MOD(10, 3) returns the remainder of 10 divided by 3, which is 1.

Date Functions
1. NOW(): The NOW() function returns the current date and time.
2. DATE(): The DATE() function returns the current date.
3. MONTH(): The MONTH() function returns the month from a given date.
4. MONTHNAME(): The MONTHNAME() function returns the name of the month from a
given date.
5. YEAR(): The YEAR() function returns the year from a given date.
6. DAY(): The DAY() function returns the day of the month from a given date.
7. DAYNAME(): The DAYNAME() function returns the name of the day of the week from
a given date.

String/Text Functions
1. UCASE()/UPPER()
● The UCASE() or UPPER() function converts a string to uppercase.
● For instance, UCASE('hello') returns 'HELLO'.

2. LCASE()/LOWER()
● The LCASE() or LOWER() function converts a string to lowercase.
● For example, LCASE('WORLD') returns 'world'.

3. MID()/SUBSTRING()/SUBSTR()
● The MID(), SUBSTRING(), or SUBSTR() function extracts a substring from a
string.
● It takes parameters for the source string, the starting position, and the length.
For example, MID('Hello World', 7, 5) returns the substring 'World'.

4. LENGTH()
● The LENGTH() function returns the length of a string.
● For example, LENGTH('Hello') returns 5.

5. LEFT()
● The LEFT() function extracts a specified number of characters from the
beginning of a string.
● For example, LEFT('Hello', 3) returns 'Hel'.

6. RIGHT()
● The RIGHT() function extracts a specified number of characters from the end of
a string.
● For example, RIGHT('Hello', 3) returns 'llo'.

7. INSTR()
● The INSTR() function returns the position of a substring within a string.
● For example, INSTR('Hello World', 'World') returns 7.

8. LTRIM()
● The LTRIM() function removes leading spaces from a string.
● For instance, LTRIM(' Hello') returns 'Hello'.

9. RTRIM()
● The RTRIM() function removes trailing spaces from a string.
● For example, RTRIM('Hello ') returns 'Hello'.

10. TRIM()
● The TRIM() function removes leading and trailing spaces from a string.
● For instance, TRIM(' Hello ') returns 'Hello'.