Unit 2 (RESTRICTING AND SORTING DATA )

TOPIC Objective:-

•  Limit the rows retrieved by a Query
•  Sort the rows retrieved by a Query

Through SELECT statement we can do a lot of selection and filtration to our search query, so we need to use some CLAUSES like (WHERE, ORDER BY), CONDITIONS like (BETWEEN, IN, LIKE, NULL) and some OPERATORS like (AND, OR, NOT).

So This Section covers following topics:

1) WHERE Clause
2) Comparison Operator
3) BETWEEN Condition
4) IN Condition
5) LIKE Condition
6) NULL Condition
7) Logical Conditions  ( AND, OR, NOT Operators)
8) AND Operator
9) OR Operator
10) NOT Operator
11) Operator Precedence
12) ORDER BY Clause
13) SORTING

TWO Tables is used for all operations (emp and dept)

EMP TABLE:

DEPT TABLE: 

DISTINCT - Eliminating Duplicate Rows

The default display of queries is all rows, including duplicate rows.

Eliminating Duplicate Rows

Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.

Note:

You can specify multiple columns after the DISTINCT clause. The DISTINCT clause affects all the selected columns, and the result is every distinct combination of the columns.

EX: 

Without DISTINCT the above output:  (Notice the difference)

Literal Character String in SELECT statement

• A literal is a character, a number or a date included in the SELECT statement list.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output once for each row returned.

Using Literal Character String:

The Example above displays the names and job codes of all employees. The column has the heading Employee Details. Notice the spaces between the single quote marks in the select statement. The spaces improves the readability of the output. 

Concatenation Operator in SELECT statement

A concatenation operator:

• Concatenates columns or character strings to other columns.
• is represented by two vertical bar ( || )
• Creates a resultant column that is character expression.

Using the Concatenation Operator:

In the above example ename and job are concatenated, and they are given alias Employees. Notice that the ename and job code are combined to make a single output column.

The AS keyword before the alias name makes the SELECT clause easier to read.

ALIAS - Column Aliases in SELECT statement

A column alias:

• Renames a column heading.
• Is useful with calculations.
• AS keyword is optional between the column name and alias.
• Requires double quotation marks if it contains spaces or special characters or is case sensitive

Using Column Aliases (with example)

Operator Precedence in SELECT statement

*      /     +     -

• Multiplication and division take priority over addition and subtraction
• Operators of the same priority are evaluated from left to right 
• Parentheses are used to force prioritized evaluation and to clarify statements

Operator Precedence with example

SELECT ename,sal,12*sal+100 

FROM emp;

SELECT ename,sal,12*(sal+100)

FROM emp;

In the above examples parentheses makes a lot of differences in arithmetic calculation.

Arithmetic Expressions in SELECT statement

You may need to modify the way in which data is displayed, perform calculations, etc.

   Operator       Description

     +                     Add
      -                   Subtract
      *                  Multiply
      /                   Divide

Using Arithmetic Operators 

If we need to show Annual salary of employes then;

Another example:

Selecting Specific Columns

In the above example we 've selected only 3 columns from the table.

Errors: 

If we want to select column that is not in that table then SQL returns an error. 

Example:

Basic Select Statement or Selecting ALL Columns

Syntax of SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],....}
FROM table;

> SELECT identifies what columns
> FROM identifies which table

Selecting All Columns

SELECT * 

FROM dept;

Result:

Unit 1 :- SELECT Staments (With Example)

A SELECT statement retrieves information from the database. Using a SELECT statement you can do the following things:

> Projection: You can choose as few or many columns of the table as you required. (Column filtration)
> Selection: You can use various criteria to restrict the rows that you see. (Row filtration)
> Joining: You can use the join capability in SQL to bring together data that is stored in different tables by creating a link between them.

In this section I am going to use two table (emp and dept) shown below.
Here are the topics that I'll cover in SELECT Statement Section.

1) Basic Select Statement or Selecting ALL Columns
2) Selecting Specific Columns
3) Use of Arithmetic Expressions in SELECT statement.
4) Operator Precedence in SELECT statement and using Parenthesis
5) Column Aliases in SELECT statement. (With Example)
6) Concatenation Operator in SELECT statement (With Example)
7) Literal Character String in SELECT statement (With Example)
8) DISTINCT - Eliminating Duplicate Rows (With Example)

In this Tutorial I've used TWO TABLES named: emp and dept.
Here are the Structure of Two Table:  ( CLICK on the image to see better)

Data in EMP and DEPT Table: ( CLICK on the image to see better)

Well Known RDBMS

There are many popular RDBMS available to work with. This tutorial gives a brief overview of few most popular RDBMS. This would help you to compare their basic features:

MySQL

MySQL is open source SQL database, which is developed by Swedish company MySQL AB. MySQL is pronounced "my ess-que-ell," in contrast with SQL, pronounced "sequel."

MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.

MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user, and robust SQL database server.

MS SQL Server

MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are:

·         T-SQL.

·         ANSI SQL.

ORACLE

It is very large and multi-user database management system. Oracle is a relational database management system developed by 'Oracle Corporation'.

Oracle works to efficiently manage its resource, a database of information, among the multiple clients requesting and sending data in the network.

It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.

MS- ACCESS

This is one of the most popular Microsoft products. Microsoft Access is entry-level database management software. MS Access database is not only an inexpensive but also powerful database for small-scale projects.

MS Access uses the Jet database engine which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL).

MS Access comes with the professional edition of MS Office package. MS Access has easy to use intuitive graphical interface.

Well-known DBMSs include MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Microsoft Access, Oracle, SAP, dBASE, FoxPro, and IBM DB2.

SQL RDBMS Concept - What is what in SQL ?

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is table ?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

Remember, a table is the most common and simplest form of data storage in a relational database. Following is the example of a CUSTOMERS table:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

What is field?

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record, or row?

A record, also called a row of data, is each individual entry that exists in a table. For example there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table:

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

+----+----------+-----+-----------+----------+

A record is a horizontal entity in a table.

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

For example, a column in the CUSTOMERS table is ADDRESS which represents location description and would consist of the following:

+-----------+

| ADDRESS   |

+-----------+

| Ahmedabad |

| Delhi     |

| Kota      |

| Mumbai    |

| Bhopal    |

| MP        |

| Indore    |

+----+------+

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank which means A field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Contraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

·         NOT NULL Constraint: Ensures that a column cannot have NULL value.

·         DEFAULT Constraint : Provides a default value for a column when none is specified.

·         UNIQUE Constraint: Ensures that all values in a column are different.

·         PRIMARY Key: Uniquely identified each rows/records in a database table.

·         FOREIGN Key: Uniquely identified a rows/records in any another database table.

·         CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

·         INDEX: Use to create and retrieve data from the database very quickly.

Data Integrity:

The following categories of the data integrity exist with each RDBMS:

·         Entity Integrity : There are no duplicate rows in a table.

·         Domain Integrity : Enforces valid entries for a given column by restricting the type, the format, or the range of values.

·         Referential integrity : Rows cannot be deleted, which are used by other records.

·         User-Defined Integrity : Enforces some specific business rules that do not fall into entity, domain, or referential integrity.

SQL Commands, (DDL,DML,DCL,DQL,TCL)

SQL Commands:

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be classified into groups based on their nature:

DDL - Data Definition Language:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language:

Command	Description
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DCL - Data Control Language:

Command	Description
GRANT	Gives a privilege to user
REVOKE	Takes back privileges granted from user

DQL - Data Query Language:

Command	Description
SELECT	Retrieves certain records from one or more tables.

Transaction Control Language (TCL) –

These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT