SQL?

 SQL?

 What is SQL?

SQL is a standard language for accessing databases.

SQL stands for Structured Query Language

SQL lets you access and manipulate databases

SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

SQL can execute queries against a database

SQL can retrieve data from a database

SQL can insert records in a database

SQL can update records in a database

SQL can delete records from a database

SQL can create new databases

SQL can create new tables in a database

SQL can create stored procedures in a database

SQL can create views in a database

SQL can set permissions on tables, procedures, and views

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

1. Creating a database;

  Create database <databasename>;

2. Using database;

  Use <database name>;

3. Showing database in use

 Select database();

 =

4. Show all the databases created

 Show databases;

5. To show tables createde

 Show tables;

6. Creating a table

Create table <tablename>

   (<columnname1> <datatype> (<size>),

    <columnname2> <datatype> (<size>))/

7.      Use of describe statement

Describe <tablename>;

8. Inserting values into tables;

Insert into <tablename> (<columnname1, <columnname2>…) values(<expression1>,<expression2>….),(<expression1>,<expression2>);

9. Select statements

a.      Selecting all the rows of a table;

Select * from <tablename>;

b.      Selecting particular columns

c.      Select <column1>,<column2> from <tablename>;

d.      Selecting some rows and all columns

Select *from <tablename> where <condition>;

e.      Selecting some rows and some columns

Select <columnname1>,<columnname2> from <tablename> where <condition>;

10. Eliminating duplicate rows using distinct

 In a table, some of the columns may contain duplicate values. This is not a problem, however,    sometimes you will want to list only the different (distinct) values in a table.

                  The DISTINCT keyword can be used to return only distinct (different) values.

             Select distinct * from <tablename>;

             Select distinct <coumnname> from <tablename>;

11. Sorting data in a table

Ascending:

Select *from <tablename> order by <columnname> asc;

Descending

Select *from <tablename> order by <columnname> desc;

   12. Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Use of between for range searching;

Use of like for pattern matching

  

Use of in and not in

The arithmetic operator = compares a single value to another  value . In case a value needs to be compared with a list of values then the ‘IN’ predicate is used. One can use a single value against multiple values by using the IN predicate..

13.The AND & OR Operators (logical operators)

The AND operator displays a record if both the first condition and the second condition is true. The OR operator displays a record if either the first condition or the second condition is true.

14. The UPDATE Statement :The UPDATE statement is used to update existing records in a table.

UPDATE <tablename>

SET column1=value, column2=value2,...

WHERE some_column=some_value

15.Modifying the structure of the table;

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE table_name

ADD column_name datatype

16.To add a column in between columns:

Alter table tablename add column columname datatype(size) after columname;

17.To delete a column in a table,  

ALTER TABLE table_name

DROP COLUMN column_name

18.Modifying existing columns:

Alter table tablename

Modify (columnname datatype(size));

Using the alter table clause

We cannot change the name of the table, change the name of the column, decrease the size of the column if table data exist.

19.Renaming the table

Rename tablename oldname to newname;

20.Use of limit to show a range of values from top:

The limit clause is used to specify the number of records to return. The limit clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

SELECT column_name(s)

FROM table_name

LIMIT number;

21.Deleting the table;

Drop table tablename;

22.Aggregate function;

1.      Sum

2.Max(column)

3.Min(column)

4.Count(*)

     5.Avg(marks);

     6. select lcase(columnname)from tablename

     7. select ucase(columnname)from tablename

    8. group by

Group by: The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

Having clause:

Join;

1.Cartesian product;

Inner join;

SQL INNER JOIN Keyword

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

Left join;

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

Right join:

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SQL RIGHT JOIN

SyntaxSELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

Full join:

Set operation:

1. Union

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

Set difference:

u 

 intersection:

Sub queries:

A subquery is a form of a sql statement that appears inside another sql statement. It is also termed as nested query. The statement containing a subquery is called a parent statement. The parent statement uses the rows returned by the subquery.

Eg.

Q. write sql to select the record of only those employees who works in same department as employee ram works.

SQL CONSTRAINT:

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

We will focus on the following constraints:

NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

To DROP a PRIMARY KEY Constraint

MySQL:ALTER TABLE tablename

DROP PRIMARY KEY

Foreign key

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

UNIQUE (P_Id)

)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

)





-Thanks for providing this notes (shova niroula mam)