Structured Query Language (SQL) - Notes | Class 12 Computer Science

Chapter 12 - Structured Query Language

Contents

Previous - Chapter

Introduction to SQL

This section covers the basics of SQL.Here we shall learn what is Structured Query Language (SQL), need for SQL, its advantages, categories of SQL commands – DDL,DML and DCL. data types used in SQL, aggregate functions and more.

1. What is SQL?

SQL stands for Structured Query Language. It is a language used to create, store, retrieve, modify and manage data in a relational database.

A relational database is a database that contains information in the form of tables where tables are connected to each other using links called relationship. SQL allows us to work with these tables easily.

Example

For example, suppose a school maintains a table named STUDENT:

database table

Using SQL, we can:

  • create the STUDENT table
  • insert new student records
  • change marks
  • delete records
  • search for students
  • calculate average marks
  • arrange records in ascending or descending order

SQL is widely used with relational database management systems such as MySQL, Oracle, PostgreSQL, SQL Server, etc.

Advantages of SQL

SQL is popular because it makes database operations easier and more systematic. Now let us see what are the advantages of using SQL

  1. Easy to learn
    SQL uses simple English-like commands such as SELECT, INSERT, UPDATE and DELETE making it easy to learn
  2. Easy to retrieve data
    We can quickly search for particular records from a large table.
  3. Easy to modify data
    Existing records can be changed easily using the UPDATE command.
  4. Works with large databases
    SQL can handle a large amount of data efficiently.
  5. Provides security
    Access to database objects can be controlled using commands such as GRANT and REVOKE.
  6. Reduces manual work
    SQL functions can perform calculations such as total, average, maximum and minimum automatically using aggregate functions

Categories of SQL Commands

SQL commands are divided into the following categories:

  1. DDL (Data Definition Language)
  2. DML (Data Manipulation Language)
  3. DCL (Data Control Language)

DDL (Data Definition Language)

DDL stands for Data Definition Language. It is used to create and modify the structure of database objects such as tables, views, indexes, etc.

Important DDL commands include:

  • CREATE
  • ALTER
  • DROP

DML (Data Manipulation Language)

DML stands for Data Manipulation Language.It is used to insert, modify, delete and retrieve records from a table. It is used to interact or work with the records stored inside a table.

Important DML commands are:

  • INSERT
  • UPDATE
  • DELETE
  • SELECT

DCL (Data Control Language)

DCL stands for Data Control Language. It is used to control the access and permissions given to users of databaseThe commonly used DCL commands are:

GRANT

It gives specific privileges to a user.

GRANT SELECT ON STUDENT TO user1;

REVOKE

It removes previously given privileges.

REVOKE SELECT ON STUDENT FROM user1;

Data types in SQL

Data type

Description

Example

INTEGER

Used to store whole numbers without decimal

Rollno integer

NUMERIC

Used to represent decimal numbers

Marks numeric

DECIMAL

Used to represent decimal numbers

Salary decimal(10,2)

CHAR

Used to store fixed-length character data. It can hold letters, numbers and special characters.

Gender CHAR(1)

 

VARCHAR/

VARCHAR2

VARCHAR is used to store variable-length character data. It can hold letter, numbers or special characters

Name VARCHAR(30)

 

DATE

The DATE data type is used to store date values.

DOB DATE

 

Creating a table

The CREATE TABLE command is used to create a new table.

Syntax

CREATE TABLE table_name

(

    column1 datatype[size],

    column2 datatype[size],

    column3 datatype[size]

);

Example

CREATE TABLE STUDENT

(

    RollNo INTEGER,

    Name VARCHAR(30),

    Class INTEGER,

    Marks DECIMAL

);

This creates a table named STUDENT with four columns.

Constraints

A constraint is a rule or condition applied to a column to control the type of data that can be entered into a table. Constraints are needed in order to  maintain the accuracy and reliability of data

Types of Constraints

Some of the constraints are:

  • Primary key constraint
  • Unique constraint
  • Not Null constraint
  • Default constraint
  • Check constraint

Primary key constraint

A primary key is used to uniquely identify each record in a table.

A primary key:

  • cannot contain duplicate values
  • cannot contain NULL values

Example:

CREATE TABLE STUDENT

(

    RollNo NUMBER PRIMARY KEY,

    Name VARCHAR(30),

    Marks NUMBER

);

Here, RollNo uniquely identifies each student

UNIQUE Constraint

The UNIQUE constraint ensures that duplicate values are not entered in a column.

Example:

CREATE TABLE STUDENT

(

    RollNo NUMBER UNIQUE,

    Name VARCHAR(30),

    Marks NUMBER

);

Here, two students cannot have the same RollNo.

NOT NULL Constraint

The NOT NULL constraint does not allow a column to contain a NULL value. NOT NULL means NOT BLANK

Example:

CREATE TABLE STUDENT

(

    RollNo NUMBER NOT NULL,

    Name VARCHAR(30) NOT NULL,

    Marks NUMBER

);

Here, RollNo and Name must contain values.

DEFAULT Constraint

The DEFAULT constraint automatically assigns a value when no value is supplied.

Example:

CREATE TABLE STUDENT

(

    RollNo NUMBER,

    Name VARCHAR(30),

    City VARCHAR(20) DEFAULT ‘Dibrugarh’

);

If the city is not specified while inserting a record, Dibrugarh will be stored automatically

CHECK Constraint

The CHECK constraint ensures that the value entered satisfies a particular condition.

Example:

CREATE TABLE STUDENT

(

    RollNo NUMBER,

    Name VARCHAR(30),

    Marks NUMBER CHECK (Marks >= 0 AND Marks <= 100)

);

This prevents marks below 0 or above 100 from being entered

DROP TABLE

The DROP TABLE command is used to remove a table completely from the database.

Syntax

DROP TABLE table_name;

Example

DROP TABLE STUDENT;

This removes the STUDENT table and its data.

Remember: DROP removes the table itself, not just the records

ALTER TABLE

The ALTER TABLE command is used to modify the structure of an existing table.

It can be used to :

  • Add a new column
  • Change the size of existing column

For example, a new column can be added to a table.

Example

ALTER TABLE STUDENT ADD City VARCHAR(20);

This adds a new column named City.

INSERT Command

The INSERT command is used to add new records to a table.

Syntax

INSERT INTO table_name VALUES (value1, value2, value3);

Example

INSERT INTO STUDENT VALUES (101, ‘Rohan’, 12, 85);

Another record can be inserted as:

INSERT INTO STUDENT VALUES (102, ‘Priya’, 12, 91);

 

Inserting values into selected columns

We can also specify the column names.

INSERT INTO STUDENT (RollNo, Name, Marks) VALUES (103, ‘Aman’, 78);

 

This is useful when we do not want to provide values for every column.

UPDATE Command

The UPDATE command is used to modify existing records.

Syntax

UPDATE table_name SET column_name = new_value WHERE condition;

Example

Suppose Rohan’s marks  whose rollno is 101 were entered incorrectly as 85 instead of 88. To change it, the command would be

UPDATE STUDENT SET Marks = 88 WHERE RollNo = 101;

The marks of the student with RollNo 101 will be changed to 88.

Important

Always be careful while using UPDATE.

For example:

UPDATE STUDENT SET Marks = 90;

This changes the marks of all students to 90 because there is no WHERE condition

DELETE Command

The DELETE command is used to remove records from a table.

Syntax

DELETE FROM table_name WHERE condition;

Example

DELETE FROM STUDENT WHERE RollNo = 102;

This deletes the record of the student whose RollNo is 102.

Important

If WHERE is omitted:

DELETE FROM STUDENT;

all records are deleted, but the table structure remains

SELECT Command

The SELECT command is used to retrieve information from a table.

To display all columns of a table the command is

SELECT * FROM STUDENT;

Here, * means all columns.

To display selected columns only the command is

SELECT Name, Marks FROM STUDENT;

Only the Name and Marks columns will be displayed.

DISTINCT

DISTINCT is used to display only different or unique values.

Suppose the STUDENT table contains:

RollNo

Name

City

101

Riya

Dibrugarh

102

Rahul

Dibrugarh

103

Ankit

Tinsukia

104

Priya

Dibrugarh

The query:

SELECT DISTINCT City FROM STUDENT;

will display:

Dibrugarh

Tinsukia

The repeated value Dibrugarh appears only once.

WHERE Clause

The WHERE clause is used to specify a condition for selecting records.

Example:

SELECT * FROM STUDENT WHERE Marks > 80;

This displays students who scored more than 80 marks.

Another example:

SELECT Name, Marks  FROM STUDENT WHERE Class = 12;

IN operator

The IN operator is used when we want to compare a value with several possible values.

Example:

SELECT * FROM STUDENT WHERE City IN (‘Dibrugarh’, ‘Tinsukia’);

This displays students whose city is either Dibrugarh or Tinsukia.

BETWEEN Operator

The BETWEEN operator is used to select values within a specified range.

For example:

SELECT * FROM STUDENT WHERE Marks BETWEEN 60 AND 80;

This selects students whose marks are between 60 and 80.

BETWEEN includes both the starting and ending values.

So, 60 and 80 are also included.

ORDER BY

The ORDER BY clause is used to arrange the result in ascending or descending order.

Ascending order

SELECT * FROM STUDENT ORDER BY Marks ASC;

ASC means ascending order.

Descending order

SELECT * FROM STUDENT ORDER BY Marks DESC;

DESC means descending order.

If ASC or DESC is not specified, ascending order is normally used

GROUP BY

The GROUP BY clause is used to arrange rows having the same value into groups.

It is commonly used with aggregate functions such as SUM(), AVG(), COUNT(), MAX() and MIN().

Suppose we have a table called EMPLOYEE:

EmpNo

Name

Department

Salary

1

Amit

Sales

25000

2

Riya

Sales

30000

3

Rahul

IT

40000

4

Neha

IT

35000

To find the total salary department-wise:

SELECT Department, SUM(Salary) FROM EMPLOYEE GROUP BY Department;

The salaries are grouped according to the department before the total is calculated

HAVING Clause

The HAVING clause is used to apply a condition to groups created by GROUP BY.

For example:

SELECT Department, AVG(Salary) FROM EMPLOYEE

GROUP BY Department

HAVING AVG(Salary) > 30000;

This displays only those departments whose average salary is greater than 30,000

Aggregate Functions

Aggregate functions perform calculations on a group of values and return a single result.

The important aggregate functions are:

  • SUM()
  • AVG()
  • COUNT()
  • MAX()
  • MIN()

Function Name

Purpose

Example

SUM( )

calculates the total of numeric values

SELECT SUM(Marks)FROM STUDENT;

AVG( )

calculates the average of numeric values.

SELECT AVG(Marks) FROM STUDENT;

COUNT( )

counts records or values

SELECT COUNT(*)FROM STUDENT;

MAX( )

returns the largest value

SELECT MAX (Marks) FROM STUDENT;

MIN( )

returns the smallest value

SELECT MIN (Marks) FROM STUDENT; 

JOIN OPERATION

In a relational database, information is often stored in more than one table.A join operation is used to combine data from two or more tables

For example, consider the following two tables.

STUDENT

RollNo

Name

Class

101

Riya

12

102

Rahul

12

103

Ankit

12

MARKS

RollNo

Subject

Marks

101

Computer

88

102

Computer

76

103

Computer

91

Both tables contain the column RollNo.

We can use this common column to obtain information from both tables.

SELECT STUDENT.Name, MARKS.Marks FROM STUDENT, MARKS WHERE STUDENT.RollNo = MARKS.RollNo;

The result will be:

Name

Marks

Riya

88

Rahul

76

Ankit

91

UNION

The UNION operator is used to combine the results of two SELECT queries into one result.

For example, suppose we have

STUDENT_A

Rollno

Name

1

Riya

2

Rahul

STUDENT_B

Rollno

Name

3

Ankit

2

Rahul

We can write:

SELECT Name FROM STUDENT_A

UNION

SELECT Name FROM STUDENT_B;

Result:

Rollno

Name

1

Riya

2

Rahul

3

Ankit

Notice that Rahul appears only once because UNION removes duplicate rows.

Important rule for UNION

The two SELECT queries should have:

  • the same number of columns
  • corresponding columns with compatible data types

Cartesian Product

A Cartesian product combines every row of the first table with every row of the second table.

It can be obtained by writing two tables in the FROM clause without a joining condition.

Example:

SELECT * FROM STUDENT, MARKS;

If STUDENT contains 3 rows and MARKS contains 3 rows:

Number of resulting rows = 3 × 3 = 9

Every student record is combined with every marks record.

Table name -Student

RollNo

StdName

Course

101

Meenakshi

DCA

102

Radhika

DCA

103

abhinav

BCA

Tablename – Faculty

FacultyNo

Faculty-Name

Subject

11

Arun Verma

C++

12

KL Arora

Java

 

 Student x Faculty

RollNo

StdName

Course

FacultyNo

Inst-Name

Subject

101

Meenakshi

DCA

11

Arun Verma

C++

101

Meenakshi

DCA

12

KL Arora

Java

102

Radhika

DCA

11

Arun Verma

C++

102

Radhika

DCA

12

KL
Arora

Java

103

abhinav

BCA

11

Arun Verma

C++

103

abhinav

BCA

12

KL
Arora

Java

Important Board Examination Points

  • SQL is used to create and manipulate a relational database.
  • There are 3 categories of SQL commands - DDL, DML ,DCL
  • Constraint is a rule or condition applied to a column of a table
  • Primary key is used to uniquely identify a record
  • Aggregate functions perform calculations on a group of values and return a single result.
Previous - Database Concepts Important Questions - SQL Next - Boolean Algebra
Scroll to Top