basics

Lecturer-15

Query Languages-Part I

In this chapter we want to emphasize that SQL is both deep and wide. Deep in the sense that it is implemented at many levels of database communication, from a simple Access form list box right up to high-volume communications between mainframes. SQL is widely implemented in that almost every DBMS supports SQL statements for communication. The reason for this level of acceptance is partially explained by the amount of effort that went into the theory and development of the standards.

What is SQL?
Structured Query Language, commonly abbreviated to SQL and pronounced as “sequel”, is not a conventional computer programming language in the normal sense of the phrase. It allows users to access data in relational database management systems. SQL is about data and results, each SQL statement returns a result, whether that result be a query, an update to a record or the creation of a database table. SQL is most often used to address a relational database, which is what some people refer to as a SQL database.So in brief we can describe SQL as follows:


• SQL stands for Structured Query Language
• SQL allows you to access a database
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database
• SQL is easy to learn
Creating a Database

Many database systems have graphical interfaces which allow developers (and users) to create, modify and otherwise interact with the underlying database management system

(DBMS). However, for the purposes of this chapter all interactions with the DBMS will be via SQL commands rather than via menus.
SQL Commands
There are three groups of commands in SQL:
1. Data Definition
2. Data Manipulation and
3. Transaction Control

Characteristics Of SQL Commands
Here you can see that SQL commands follow a number of basic rules:
• SQL keywords are not normally case sensitive, though this in this tutorial all commands (SELECT, UPDATE etc) are upper-cased.
• Variable and parameter names are displayed here as lower-case.
• New-line characters are ignored in SQL, so a command may be all on one line or broken up across a number of lines for the sake of clarity.
• Many DBMS systems expect to have SQL commands terminated with a semi-colon character.

SQL DATA DEFINITION LANGUAGE (DDL)
The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
How to create table
Creating a database is remarkably straightforward. The SQL command which you have to give is just: CREATE DATABASE dbname;
In this example you will call the database RaiUniv, so the command which you have to give is: CREATE DATABASE RaiUniv;

Once the database is created it, is possible to start implementing the design sketched out previously.
So you have created the database and now it's time to use some SQL to create the tables required by the design. Note that all SQL keywords are shown in upper case, variable names in a mixture of upper and lower case.
The SQL statement to create a table has the basic form: CREATE TABLE name( col1 datatype, col2 datatype, …);
So, to create our User table we enter the following command:
CREATE TABLE User (FirstName TEXT, LastName TEXT, UserID TEXT, Dept TEXT, EmpNo INTEGER, PCType TEXT );
The TEXT datatype, supported by many of the most common DBMS, specifies a string of characters of any length. In practice there is often a default string length which varies by product. In some DBMS TEXT is not supported, and instead a specific string length has to be declared. Fixed length strings are often called CHAR(x), VCHAR(x) or VARCHAR(x), where x is the string length. In the case of INTEGER there are often

multiple flavors of integer available. Remembering that larger integers require more bytes for data storage, the choice of int size is usually a design decision that ought to be made up front.

Lecturer-16

How to Modify table
Once a table is created it's structure is not necessarily fixed in stone. In time requirements change and the structure of the database is likely to evolve to match your wishes. SQL can be used to change the structure of a table, so, for example, if we need to add a new field to our User table to tell us if the user has Internet access, then we can execute an SQL ALTER TABLE command as shown below: ALTER TABLE User ADD COLUMN Internet BOOLEAN;

To delete a column the ADD keyword is replaced with DROP, so to delete the field we have just added the SQL is: ALTER TABLE User DROP COLUMN Internet;
How to delete table
If you have already executed the original CREATE TABLE command your database will already contain a table called User, so let's get rid of that using the DROP command: DROP TABLE User;
And now we'll recreate the User table we'll use throughout the rest of this tutorial: CREATE TABLE User (FirstName VARCHAR (20), LastName VARCHAR (20), UserID VARCHAR(12) UNIQUE, Dept VARCHAR(20), EmpNo INTEGER UNIQUE, PCType VARCHAR(20);


SQL DATA MANIPULATION LANGUAGE (DML)

SQL language also includes syntax to update, insert, and delete records.
These query and update commands together form the Data Manipulation Language (DML) part of SQL:
INSERT INTO - inserts new data into a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
SELECT - extracts data from a database table
How to Insert Data

Having now built the structure of the database it is time to populate the tables with some data. In the vast majority of desktop database applications data entry is performed via a user interface built around some kind of GUI form. The form gives a representation of the information required for the application, rather than providing a simple mapping onto the tables. So, in this sample application you would imagine a form with text boxes for the user details, drop-down lists to select from the PC table, drop-down selection of the software packages etc. In such a situation the database user is shielded both from the underlying structure of the database and from the SQL which may be used to enter data into it. However we are going to use the SQL directly to populate the tables so that we

can move on to the next stage of learning SQL.
The command to add new records to a table (usually referred to as an append query), is: INSERT INTO target [(field1[, field2[, ...]])] VALUES (value1[, value2[, ...]);
So, to add a User record for user Jim Jones, we would issue the following INSERT query: INSERT INTO User (FirstName, LastName, UserID, Dept, EmpNo, PCType) 6 VALUES ("Jim", "Jones", "Jjones","Finance", 9, "DellDimR450");

Obviously populating a database by issuing such a series of SQL commands is both tedious and prone to error, which is another reason why database applications have front-ends. Even without a specifically designed front-end, many database systems - including MS Access - allow data entry direct into tables via a spreadsheet-like interface.
The INSERT command can also be used to copy data from one table into another. For example, The SQL query to perform this is:
INSERT INTO User ( FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet )

Lecturer-17
Contd..SELECT FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet FROM NewUsers;
How to Update Data

The INSERT command is used to add records to a table, but what if you need to make an amendment to a particular record? In this case the SQL command to perform updates is the UPDATE command, with syntax: UPDATE table SET newvalue WHERE criteria;
For example, let's assume that we want to move user Jim Jones from the Finance department to Marketing. Our SQL statement would then be: UPDATE User SET Dept="Marketing"
WHERE EmpNo=9; The UPDATE command can be used for more than just changing a single field or record at a time. The SET keyword can be used to set new values for a number of different fields, so we could have moved Jim Jones from Finance to marketing and changed the PCType as well in the same statement (SET Dept="Marketing", PCType="PrettyPC"). Or if all of the Finance department were suddenly granted Internet access then we could have issued the following SQL query: UPDATE User SET Internet=TRUE WHERE Dept="Finance";
You can also use the SET keyword to perform arithmetical or logical operations on the values. For example if you have a table of salaries and you want to give everybody a 10% increase you can issue the following command: UPDATE PayRoll SET Salary=Salary * 1.1;
How to Delete Data

Now that we know how to add new records and to update existing records it only remains to learn how to delete records before we move on to look at how we search through and collate data. As you would expect SQL provides a simple command to delete complete records. The syntax of the command is: DELETE [table.*] FROM table WHERE criteria;
Let's assume we have a user record for John Doe, (with an employee number of 99), which we want to remove from our User we could issue the following query: DELETE * FROM UserWHERE EmpNo=99;

In practice delete operations are not handled by manually keying in SQL queries, but are likely to be generated from a front end system which will handle warnings and add safe-guards against accidental deletion of records.
Note that the DELETE query will delete an entire record or group of records. If you want to delete a single field or group of fields without destroying that record then use an UPDATE query and set the fields to Null to over-write the data that needs deleting. It is also worth noting that the DELETE query does not do anything to the structure of the table itself, it deletes data only. To delete a table, or part of a table, then you have to use the DROP clause of an ALTER TABLE query.

Lecturer-18

Transaction Control Language(TCL)
The SQL Data Control Language (DCL) provides security for your database. The DCL consists of the GRANT, REVOKE, COMMIT, and ROLLBACK statements. GRANT and REVOKE statements enable you to determine whether a user can view, modify, add, or delete database information.
Working with transaction control
Applications execute a SQL statement or group of logically related SQL statements to perform a database transaction. The SQL statement or statements add, delete, or modify data in the database.
Transactions are atomic and durable. To be considered atomic, a transaction must successfully complete all of its statements; otherwise none of the statements execute. To be considered durable, a transaction's changes to a database must be permanent.
Complete a transaction by using either the COMMIT or ROLLBACK statements. COMMIT statements make permanent the changes to the database created by a transaction. ROLLBACK restores the database to the state it was in before the transaction was performed.

SQL Transaction Control Language Commands (TCL.)
This page contains some SQL TCL. commands that I think it might be useful. Each command's description is taken and modified from the SQLPlus help. They are provided as is and most likely are partially described. So, if you want more detail or other commands, please use HELP in the SQLPlus directly.
COMMIT
PURPOSE:
To end your current transaction and make permanent all changes performed in the transaction. This command also erases all savepoints in the transaction and releases the transaction's locks. You can also use this command to manually commit an in-doubt distributed transaction.

SYNTAX: COMMIT [WORK] [ COMMENT 'text' FORCE 'text' [, integer] ]
Where:
• WORK : is supported only for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.
• COMMENT : specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt. number (SCN). If you omit the integer, the transaction is committed using the current SCN. COMMIT statements using the FORCE clause are not supported in PL/SQL.

• FORCE : manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the integer to specifically assign the transaction a system change

PREREQUISITES:
You need no privileges to commit your current transaction. To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
Example:
To commit your current transaction, enter SQL> COMMIT WORK;
Commit complete.
ROLLBACK

PURPOSE:
To undo work done in the current transaction. You can also use this command to manually undo the work done by an in-doubt distributed transaction.
SYNTAX: ROLLBACK [WORK] [ TO [SAVEPOINT] savepoint FORCE 'text' ]
Where:

• WORK : is optional and is provided for ANSI compatibility.
• TO : rolls back the current transaction to the specified savepoint. If you omit this clause, the ROLLBACK statement rolls back the entire transaction.
• FORCE : manually rolls back an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. ROLLBACK statements with the FORCE clause are not supported in PL/SQL.
PREREQUISITES:

To roll back your current transaction, no privileges are necessary. To manually roll back an in-doubt distributed transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
Example:
To rollback your current transaction, enter SQL> ROLLBACK;
Rollback complete.
Creating users

This section covers the following information:
• Creating database administrators
• Creating users
The CREATE statement is not a part of the Data Control Language, but rather the Data Definition Language. This chapter addresses the CREATE statement as it relates to the

This section covers the following information:
• Creating database administrators
• Creating users 12
The CREATE statement is not a part of the Data Control Language, but rather the Data Definition Language. This chapter addresses the CREATE statement as it relates to the

A user who initially creates a database becomes its default administrator. Therefore, this initial user has the authority to create other administrator accounts for that particular database.





Lecturer-19

Query Languages-Part II

Here you would learn about the various DML commands which would help to manage and manipulate the data in the database. And this may prove to be the turning point of your life which would decide whether you would become an expert Database Programmer. Let us swim together.
The Basic SELECT command
Having designed, built and populated our database with some sample data, we can now move on to what is considered the heart of SQL - the SELECT statement. This is the command that queries the database and which provides real value to any database.

Select...From
We use it to select data from the tables located in a database. Immediately, we see two keywords: we need to SELECT information FROM a table. There you have it. The most basic SQL structure:
SELECT "column_name" FROM "table_name"
To illustrate the above example, assume that we have the following table:
Table Store_Information



To select all the stores in this table, we key in,
SELECT store_name FROM Store_Information
Result:
store_name
Los Angeles
San Diego
Los Angeles
Boston


Multiple column names can be selected, as well as multiple table names.
Distinct:-
The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily means that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:
SELECT DISTINCT "column_name" FROM "table_name"
For example, to select all distinct stores in Table Store_Information,
Table Store_Information





SELECT DISTINCT store_name FROM Store_Information
Result:
store_name
Los Angeles
San Diego
Boston

Where:-
Next, we might want to conditionally select the data from a table. For example, we may want to only retrieve stores with sales above $1,000. To do this, we use the WHERE keyword.

The syntax is as follows:
SELECT "column_name" FROM "table_name" WHERE "condition"
With the WHERE clause, the following operators can be used:






Note: In some versions of SQL the <> operator may be written as !=
For example, to select all stores with sales above $1,000 in Table Store_Information,
Table Store_Information



SELECT store_name FROM Store_Information WHERE Sales > 1000 Result:
store_name
Los Angeles

LIKE ;-
The LIKE condition is used to specify a search for a pattern in a column.
Syntax
SELECT column FROM table


WHERE column LIKE pattern

A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
The following SQL statement will return persons with first names that start with an 'O':
SELECT * FROM Persons WHERE FirstName LIKE 'O%'

The following SQL statement will return persons with first names that end with an 'a':
SELECT * FROM Persons WHERE FirstName LIKE '%a'


The following SQL statement will return persons with first names that contain the pattern 'la':
SELECT * FROM Persons WHERE FirstName LIKE '%la%'

Lecturer-20 and Practical

SQL Aggregates

Aggregate functions that calculate summary values, such as averages and sums, from the values in a particular column and return a single value for each set of rows to which the function applies. The aggregate functions are AVG, COUNT, COUNT(*), MAX, MIN, SUM, STDEV, STDEVP, VAR, and VARP. Aggregate functions can be applied either to all rows in a table, to a subset of table rows specified by a WHERE clause, or to one or more groups of table rows specified by the GROUP BY clause.

You know SQL supports five aggregate functions, which can be applied on any attribute of a relation:





Sum:-
SELECT "function type"("column_name") FROM "table_name"
For example, if we want to get the sum of all sales from our example table,
Table Store_Information





We would type in
SELECT SUM (Sales) FROM Store_Information Result:
SUM(Sales)
$2750 6

$2750 represents the sum of all Sales entries: $1500 + $250 + $300 + $700.

Count:-
Another arithmetic function is COUNT. This allows us to COUNT up the number of row in a certain table. The syntax is,
SELECT COUNT ("column_name") FROM "table_name"
For example, if we want to find the number of store entries in our table,
Table Store_Information

We’d key in
SELECT COUNT (store_name) FROM Store_Information
Result:
Count(store_name)
4

COUNT and DISTINCT can be used together in a statement to fetch the number of distinct entries in a table. For example, if we want to find out the number of distinct stores, we'd type,

SELECT COUNT (DISTINCT store_name) FROM Store_Information
Result:
Count(DISTINCT store_name)
3


Avg:-
Find the average age of the employees in the toy department.
select avg(age)
from emp
where dname = "toy"
Min:-
Example:
For each department, find the minimum age of employees who make
more than 50K.

select dname, min(age)
from emp
where sal > 50
group by dname


Max:-
Note:Cannot mix tuple and aggregate values
Example:
Find the employee with the highest salary
select ename, sal
from emp
where sal = (select max(sal) from emp )
Example:
Count the employees and their average monthly salary for each employee category in each department

select dname, cat, count(*), avg(sal)/12
from emp
group by dname, cat

Group By:-

Now we return to the aggregate functions. Remember we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores.
The corresponding SQL syntax is,
SELECT "column_name1", SUM ("column_name2") FROM "table_name" GROUP BY "column_name1"


In our example, table Store_Information,
Table Store_Information



SQL QUERIES-Part III
Group By:-
Now we return to the aggregate functions. Remember we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores.
The corresponding SQL syntax is,
SELECT "column_name1", SUM ("column_name2") FROM "table_name" GROUP BY "column_name1"

In our example, table Store_Information,
Table Store_Information




We would key in, 1
SELECT store_name, SUM (Sales) FROM Store_Information GROUP BY store_name

Result:
store_name
SUM(Sales)
Los Angeles
$1800
San Diego
$250
Boston
$700

The GROUP BY keyword is used when we are selecting multiple columns from a table (or tables) and at least one arithmetic operator appears in the SELECT statement. When that happens, we need to GROUP BY all the other selected columns, i.e., all columns except the one(s) operated on by the arithmetic operator.
Having :-

Another thing people may want to do is to limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is typically placed near the end of SQL, and SQL statements with the HAVING clause may or may not include the GROUP BY clause. The syntax is,
SELECT "column_name1", SUM ("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithmetic function condition)

In our example, table Store_Information,
Table Store_Information


We would type,
SELECT store_name, SUM (sales) FROM Store_Information GROUP BY store_name HAVING SUM (sales) > 1500
Result:
store_name
SUM(Sales)
Los Angeles
$1800


Order By:-
The ORDER BY clause is used to sort the rows.


Example
To display the companies in alphabetical order:

SELECT Company, OrderNumber FROM Orders ORDER BY Company

Result:
Example
To display the companies in alphabetical order AND the order numbers in numerical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

Result:


Example
To display the companies in reverse alphabetical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC


Result:






Example
To display the companies in reverse alphabetical order AND the ordernumbers in numerical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

Result:



AND & OR
AND and OR join two or more conditions in a WHERE clause.
The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.




Example
Use AND to display each person with the first name equal to "Tove", and the last name equal to "Svendson":
SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'


Result:




Example
Use OR to display each person with the first name equal to "Tove", or the last name equal to "Svendson":
SELECT * FROM Persons WHERE firstname='Tove' OR lastname='Svendson'

Result:






Example
You can also combine AND and OR (use parentheses to form complex expressions):
SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson'


Result:



IN
The IN operator may be used if you know the exact value you want to return for at least one of the columns.
SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)



Table





To display the persons with LastName equal to "Hansen" or "Pettersen", use the following SQL:
SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')




BETWEEN ... AND
The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.
SELECT column_name FROM table_name WHERE column_name




BETWEEN value1 AND value2




To display the persons alphabetically between (and including) "Hansen" and exclusive "Pettersen", use the following SQL:
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'


Alias
• Column Name Alias 9
The syntax is:

SELECT column AS column_alias FROM table

• Table Name Alias
The syntax is:
SELECT column FROM table AS table_alias


Example: Using a Column Alias
This table (Persons):




And this SQL:
SELECT LastName AS Family, FirstName AS Name FROM Persons

Returns this result:


Example: Using a Table Alias
This table (Persons):

And this SQL:
SELECT LastName, FirstName FROM Persons AS Employees

Returns this result:
Table Employees:


Joins and Keys
Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.
When you look at the example tables below, notice that:
• The "Employee_ID" column is the primary key of the "Employees" table
• The "Prod_ID" column is the primary key of the "Orders" table
• The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names

Employees:





We can select data from two tables by referring to two tables, like this:

Example
Who has ordered a product, and what did they order?
SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID

Result



Example
Who ordered a printer? SELECT Employees.Name FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID AND Orders.Product='Printer'



Using Joins
OR we can select data from two tables with the JOIN keyword, like this:

• INNER JOIN
Syntax
SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID


The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
Result



• LEFT JOIN
Syntax
SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table


ON first_table.keyfield = second_table.foreign_keyfield

List all employees, and their orders - if any.
SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID


The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
Result





• RIGHT JOIN
Syntax
SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

List all orders, and who has ordered - if any.

SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
Result


Example
Who ordered a printer?
SELECT Employees.Name FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID WHERE Orders.Product = 'Printer'

Result