Revision

DATABASES AND SQL

 Ø  A database is a collection of data and contains information about a particular enterprise.

Ø  Data are the raw facts and figures which are useful to an organization.

Ø  Information is well processed data.

Ø  Field is a set of characters that represents specific data element.

Ø  Record is a collection of fields. A record can have fields of different data types.

Ø  File is a collection of similar types of records.

Ø  Table is a collection of rows and columns that contains useful data/information.

Ø  Database is a collection of logically related data along with its description.

Ø  Tuple is a row in a relation.

Ø  Attribute is a column in a relation. It is also termed as field or data item.

Ø  Degree is a number of attributes in a relation is called degree of a relation.

Ø  Cardinality is the number of tuples in a relation.

Ø  Primary Key is a key that uniquely identifies the records/tuples in a relation. This key can never be duplicated and NULL.

Ø  Foreign Key is a key that is defined as a primary key in some other relation. This key is      used to enforce referential integrity in RDBMS.

Ø  Candidate Key is a set of all attributes which can serve as a primary key in a relation.

       Alternate Key are all the candidate keys other than the primary keys of a relation.

Ø  DBA (Data Base Administrator) is a person (manager) who is responsible for defining the data base schema, setting security features in database, ensuring proper functioning of the data bases etc.

Ø  Structured Query Language is a non procedural language that is used to create, manipulate and process the databases (relations).

Processing Capabilities of SQL

The following are the processing capabilities of SQL

1.       Data Definition Language (DDL)

DDL contains commands that are used to create the tables, databases, indexes, views, sequences and synonyms etc.

e.g: Create table, create view, create index, alter table etc.

2. Data Manipulation Language (DML)

DML contains command that can be used to manipulate the data base objects and to query the           databases for information retrieval.

e.g Select, Insert, Delete, Update etc.

3. View Definition:

DDL contains set of command to create a view of a relation.

e.g: create view

4. Data Control Language:

        This language is used for controlling the access to the data. Various commands like GRANT,          REVOKE etc are available in DCL.

  5. Transaction Control Language (TCL)

       TCL includes commands to control the transactions in a data base system. The commonly used commands in TCL are COMMIT, ROLLBACK etc.

Data types of SQL

Just like any other programming language, the facility of defining data of various types is available in SQL also. Following are the most common data types of SQL.

1)       NUMBER

2)       CHAR

3)       VARCHAR / VARCHAR2

4)       DATE

5)       LONG

6)       RAW/LONG RAW

Data Models

Ø  A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organized in tables. A table is a collection of records and each record in a table contains the same fields.

Ø  The network data model differs from the relational model in that data is represented by collections of records and relationships among data are represented by links. Data is represented as arbitrary graphs.

Ø  The hierarchical data model organizes data in a tree structure. There is a hierarchy of parent and child data segments.

The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms.

DDL Commands

CREATE

The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE Statements differs for different RDBMS.

CREATE TABLE table_name (column_name1 datatype, column_name2 datatype, ... column_nameN datatype );

• table_name - is the name of the table.
• column_name1, column_name2.... - is the name of the columns
• datatype - is the data type for the column like char, date, number etc.

Example: If we want to create the employee table, the statement would be like,

CREATE TABLE employee ( id number(5), name char(20), dept char(10), age number(2), salary number(10) location char(10)  );

CREATE TABLE temp_employee SELECT * FROM employee

In the above statement, temp_employee table is created with the same number of columns and datatype as employee table.

ALTER

Syntax:

ALTER TABLE table_name ADD column_name datatype;

Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employee ADD experience number(3);

Syntax:

ALTER TABLE table_name DROP column_name;

Example: To drop the column "location" from the employee table, the query would be like

ALTER TABLE employee DROP location;

Syntax:

ALTER TABLE table_name MODIFY column_name datatype;

Example: To modify the column salary in the employee table, the query would be like

ALTER TABLE employee MODIFY salary number (15, 2);

RENAME

The SQL RENAME command is used to change the name of the table or a database object. If you change the object's name any reference to the old name will be affected. We have to manually change the old name to the new name in every reference.

Syntax

RENAME old_table_name To new_table_name;

Example: To change the name of the table employee to my_employee, the query would be like

RENAME employee TO my_employee;

DML Commands

The Data Manipulation Language (DML) is used to retrieve, insert and modify database information.

INSERT

The INSERT command in SQL is used to add records to an existing table. To add a new employee to the database, we could use a command similar to the one shown below:

INSERT INTO personal_info values ('x', 'aaa', 111, 5000)

 UPDATE

The UPDATE command can be used to modify information contained within a table, either in bulk or individually. The following SQL command could be used to quickly apply a 5% increase to all of the employees stored in the database:

UPDATE personal_info SET salary = salary * 1.05

To increase the salary of a particular employee,

UPDATE personal_info SET salary = salary + 5000 WHERE employee_id = 111

DELETE
The DELETE command with a WHERE clause can be used to remove a record from the personal_info table

DELETE FROM personal_info WHERE employee_id = 111

Select

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table.

Syntax :

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

• table-name is the name of the table from which the information is retrieved.
• column_list includes one or more columns from which data is retrieved.
• The code within the brackets is optional.

Example: To find the name of a student with id 100, the query would be like:

SELECT first_name, last_name FROM student_details WHERE id = 100;

OR Logical Operator:

If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.

Example: if you want to find the names of students who are studying either Maths or Science, the query would be like,

SELECT first_name, last_name, subject FROM student_details
WHERE subject = 'Maths' OR subject = 'Science'

The output would be like,

first_name	last_name	subject
Anjali	Bhagwat	Maths
Shekar	Gowda	Maths
Rohit	Sharma	Science
Stephen	Fleming	Science

AND Logical Operator:

If we want to select rows that must satisfy all the given conditions, we can use the logical operator, AND.

Example: To find the names of the students between the age 10 to 15 years, the query would be like:

SELECT first_name, last_name, age FROM student_details
WHERE age >= 10 AND age <= 15;

The output would be like,

first_name	last_name	age
Rohit	Sharma	10
Anjali	Bhagwat	12
Shekar	Gowda	15

"NOT" Logical Operator:

If we want to find rows that do not satisfy a condition, we can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

Example: If we want to find out the names of the students who do not play football, the query would be like:

SELECT first_name, last_name, games FROM student_details
WHERE NOT games = 'Football'

The output would be like,

first_name	last_name	games
Rohit	Sharma	Cricket
Stephen	Fleming	Cricket
Shekar	Gowda	Badminton
Priya	Chandra	Chess

Nested Logical Operators:

We can use multiple logical operators in an SQL statement. When we combine the logical operators in a SELECT statement, the order in which the statement is processed is

1) NOT
2) AND
3) OR

Example: If we want to select the names of the students who age is between 10 and 15 years, or those who do not play football, the SELECT statement would be

SELECT first_name, last_name, age, games
FROM student_details
WHERE age >= 10 AND age <= 15
OR NOT games = 'Football'

The output would be like,

first_name	last_name	age	games
Rahul	Sharma	10	Cricket
Priya	Chandra	15	Chess

In this case, the filter works as follows:

Condition 1: All the students who do not play football are selected.
Condition 2: All the students who are aged between 10 and 15 are selected.
Condition 3: Finally, the rows which satisfy atleast one of the above conditions is returned.

ORDER BY

Syntax

SELECT column-list FROM table_name [WHERE condition]

Example: If we want to sort the employee table by salary of the employee, the sql query would be.

SELECT name,salary FROM employee ORDER BY salary;

The below query sorts only the column 'salary' in descending order and the column 'name' by ascending order

SELECT name salary FROM employee ORDER BY name,salary DESC;

GROUP BY

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.

Example: If we want to know the total amount of salary spent on each department, the query would be:

SELECT dept,SUM (salary) FROM employee GROUP BY dept;

HAVING

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

Example: If we want to select the department that has total salary paid for its employees more than 25000, the sql query would be like;

SELECT dept, SUM (salary) FROM employee

GROUP BY dept HAVING SUM (salary) > 25000

 

SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

For Example: If we want the number of employees in a particular department, the query would be:

SELECT COUNT (*) FROM employee
WHERE dept = 'Electronics';

The output would be '2' rows.

If we want the total number of employees in all the department, the query would take the form:

SELECT COUNT (*) FROM employee;

The output would be '5' rows.

SQL DISTINCT (): This function is used to select the distinct rows.

For Example: If we want to select all distinct department names from employee table, the query would be:

SELECT DISTINCT dept FROM employee;

To get the count of employees with unique name, the query would be:

SELECT COUNT (DISTINCT name) FROM employee;

SQL MAX (): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an employee, the query would be:

SELECT MAX (salary) FROM employee;

SQL MIN (): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an employee, the query would be:

SELECT MIN (salary) FROM employee;

SQL AVG (): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM employee;

SQL SUM (): This function is used to get the sum of a numeric column

To get the total salary given out to the employees,

SELECT SUM (salary) FROM employee;