Handbook of Computer Science(cs) and IT

SQL

Structured Query Language (SQL) is a language that provides an interface to relation database systems. SQL was developed by IBM in the 1970, for use in system R and is a defacto standard, as well as an ISO and ANSI standard.

  • To deal with the above database objects, we need a programming language and that programming language is known as SQL.

Three subordinate languages of SQL are

Data Definition Language (DDL)

It ncludes the commands as

  • CREATE To create tables in the database.
  • ALTER To modify the existing table structure.
  • DROP To drop the table with table structure.
  • Data Manipulation Language (DML) It is used to insert, delete, update data and perform queries on these tables. Some of the DML commands are given below.
  • INSERT To insert data into the table.
  • SELECT To retrieve data from the table.
  • UDPDATE To update existing data in the table.
  • DELETE To delete data from the table.

 

 

Data Control Language (DCL)

It is used to control user’s access to the database objects. Some of the DCL commands are

DCL Commands are

 

  • GRANT Used to grant select/insert/delete access.
  • REVOKE Used to revoke the provided access.

 

 

Transaction Control Language (TCL)

It is used to manage changes affecting the data.

  • COMMIT To save the work done, such as inserting or updating                               or, deleting data to/from the table.
  • ROLLBACK To restore database to the original state, since last commit.
  • SOL Data Types SQL data types specify the type, size and forma,
    data/information that can be stored in columns and variables.

Key Points                     

  • SQL is a special-purpose programming language designed for managing data held in a Relational
  • Database Management Systems (RDBMS).
  • SQL based upon relational algebra and tuple relational calculus, SQL consist, of a data definition language and a data manipulation language.

Various Data Types in SQL

  • Data Time time-stamp
  • Char Big int Integer
  • Decimal Small int Double

There are so many other data types also.

Database Constraints

These are user defined that let us restrict the behaviours of column. We create constraints when we define a table with a SQL CREATE state

Inline Constraint

A constraint defined on the same line as its column.

 

Out of Line Constraint

A constraint defined on it’s own line in a CREATE statement. This constraint must reference the column that they constrain.

 

 

 

Key Points

  • The most common operation in SQL is the query, which is performed with the declarative SELECT statement.
  • SELECT retrieves data from one or more tables, or expressions.
  • Standard SELECT statements have no persistent effects on the
  • Queries allow the user to describe desired data, leaving the Database Management System (DBMS) responsible for planning, optimizing, and performing the physical operations necessary to produce that result as it chooses.

 

Default Constraint

It is used to insert a default value into a column, if no other value is specified at the time of insertion.

Syntax.

CREATE TABLE Empl oyee

{  

Emp_i d i nt NOT NULL,

Last__Name varchar (250).

City varchar (50)DEFAULT `BANGALURU’

}

 

DDL Commands

CREATE TABLE < Tabl e _Name>

{

Col umn_name 1 < data_type >,

Col umn_name 2 < data_type >

}

  1. 2. ALTER TABLE < Table_Name >

AL TER Column < Col umn_Name> SET NOT NULL

  1. RENAME < object_type >object_name >to <new_name >
  2. DROP TABLE <Tabl e_Name>

DML Commands

SELECT                                                            Ai, A2, A3, , An what to return

FROM                                                            R1, R2, R3, , Rm, relations or table

WHERE condition filter condition i.e., on what basis, we want to restrict outcome/result.

If we want to write the above SQL script in the form of relational calculu we use the following syntax

AI…Ancondition (R1 x R2 x…x Rm))}

 Comparison operators which we can use in filter condition are (=, > ,<, > =, < = , < >) ‘< >’means not equal to.

INSERT Statement

Used to add row (s) to the tables in a database.

INSERT INTO Employee (F_Name, L_Name)VALUES (‘Atal’,’Bihari’)

 

Key Points

  • Data values can be added either in every column in a row or in same columns in a row by specifying the columns and their data.
  • All the columns that are not listed in the column list in INSERT statement, will receive NULL.

………………………………………………………………             ………..

UPDATE Statement

It is used to modify/update or change existing data in single row, group’ rows or all the rows in a table.

 

 

Example UPDATE Employee                                An example of selective update
which will update some rows In

a table.

SET City = LUCKNOW ‘

WHERE Emp…Id BETWEEN

9 AND 15;

UPDATE Employee SET City = LUCKNOW’ ;                Example of global update                                                                                                                        which will update city                                                                                                                                                column for all the rows.

 

           

DELETE Statement

This used to delete rows from a table.

e.g.,

DELETE                                          Employee                            WHERE Example of selective delete

Emp_Id=7;

This command will delete all the

DELETE Employee

rows from Employee table.

ORDER BY Clause

This clause is used to sort the result of a query in a specific order (ascending or descending)

 

By default sorting order is ascending.

SELECT Emp_Id , Emp_Name , City FROM Empl oyee

WHERE City ‘ LUC KNOW ‘

ORDER BY Emp_Id DESC ;

 

GROUP BY Clause

is used to divide the result set into groups. Grouping can be done by a   column name or by the results of computed columns when using numeric data types.

  • The HAVING clause can be used to set conditions for the GROUP BY
  • HAVING clause is similar to the WHERE clause, but having puts conditions on groups.
  • WHERE clause places conditions on rows.
  • WHERE clause can’t include aggregate function, while HAVING conditions can do so.

e.g.,

SELECT Emp_Id, AVG (Salary)

FROM Empl oyee

GROUP BY Emp_Id

HAVING AVG (Salary) > 25000;

 

Aggregate Functions

Function Desciption
Sum() It returns total sum of the values in a column.

 

AVG() It returns average of the values in a column.

 

COUNT() Provides number of non-null values in a column
MIN() and MAX() Provides lowest and highest value respectively in a column
COUNT(*) Counts total number of rows in a table.

 

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95

Leave a Reply

Your email address will not be published. Required fields are marked *