Unit - 3
Introduction to SQL - PL/SQL
Structured Query Language (SQL) is a database query language which is used to store, retrieve and modify data in the relational database. SQL comes in different versions and forms. All of these versions are based upon the ANSI SQL.
Currently, MySQL is the most common program for database management systems used for relational database management. It is open-source, Oracle Company - supported database program. In comparison to Microsoft SQL Server and Oracle Database, the database management system is fast, scalable, and easy to use. It is widely used for building efficient and interactive server-side or web-based business applications in combination with PHP scripts.
3.1.1 Characteristics of SQL
● It is quick to learn SQL.
● For accessing data from relational database management systems, SQL is used.
● SQL will perform database queries against it.
● To describe data, SQL is used.
● SQL is used in the database to describe the data and manipulate it when appropriate.
● To build and drop the database and the table, SQL is used.
● SQL is used in a database to construct a view, a stored procedure, a function.
● SQL allows users to set tables, procedures, and display permissions.
3.1.2 Advantages of SQL
● SQL is easy to learn and use.
● SQL is non-procedural language. It means we have to specify only what to retrieve and not the procedure for retrieving data.
● SQL can be embedded within other languages using SQL modules, pre-compilers.
● Allows creating procedure, functions, and views using PL-SQL.
● Allows users to drop databases, tables, views, procedure and cursors.
● Allows users to set access permissions on tables, views and procedures.
Key takeaway:
- MySQL is the most common program for database management systems used for relational database management.
- Structured Query Language (SQL) is a database query language which is used to store, retrieve and modify data in the relational database.
- SQL comes in different versions and forms.
3.1.3 SQL data types
In order to describe the values that a column can hold, SQL Datatype is used.
Each column is needed in the database table to have a name and data type.
Fig 1: SQL data types
- Binary data types
Three kinds of binary data types are given below:
Data type | Description |
Binary | It has a fixed byte length of 8000. It includes binary data of fixed-length. |
Varbinary | It has a fixed byte length of 8000. It includes binary data of variable-length. |
Image | It has a maximum of 2,147,483,647 bytes in length. It includes binary data of variable-length. |
2. Numeric data types
The subtypes are given below:
Data type | From | To | Description |
Float | -1.79E + 308 | 1.79E + 308 | Used to specify a floating-point value |
Real | -3.40e + 38 | 3.40E + 38 | Specifies a single precision floating point number |
3. Extract numeric data types
The subtypes are given below:
Data types | Description |
Int | Used to specify an integer value. |
Smallint | Used to specify small integer value |
Bit | Number of bits to store. |
Decimal | Numeric value that can have a decimal number |
Numeric | Used to specify a numeric value |
4. Character String data types
Data types | Description |
Char | It contains Fixed-length (max - 8000 character) |
Varchar | It contains variable-length (max - 8000 character) |
Text | It contains variable-length (max - 2,147,483,647 character) |
5. Date and Time data types
Data types | Description |
Date | Used to store the year, month, and days value. |
Time | Used to store the hour, minute, and second values. |
Timestamp | Stores the year, month, day, hour, minute, and the second value. |
3.1.4 Literals
Literals are notes or the concept of representing/expressing a meaning that does not change. Literals are equivalent to constants in MySQL. When declaring a variable or executing queries, we may use a literal one.
We will explain the various forms of literal statements in this section and how they can be used in MySQL statements.
The following are the literal forms:
S.no |
Literal type & example |
1 | Numeric Literals 050 78 -14 0 +32767 6.6667 0.0 -12.0 3.14159 +7800.00 6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3 |
2 | Character Literals 'A' '%' '9' ' ' 'z' '(' |
3 | String Literals 'Hello, world!' 'Tutorials Point' '19-NOV-12' |
4 | BOOLEAN Literals TRUE, FALSE, and NULL. |
5 | Date and Time Literals DATE '1978-12-25'; TIMESTAMP '2012-10-29 12:01:01'; |
Key takeaway:
- Literals are equivalent to constants in MySQL. When declaring a variable or executing queries, we may use a literal one.
- Literals are notes or the concept of representing/expressing a meaning that does not change
3.1.5 DDL (Data Definition Language)
DDL queries are used to define and manipulate the structure of tables. These commands will primarily be used by database administrators during the setup and removal phases of a database project.
DDL contains:
1. CREATE command to create a new table
2. ALTER command to modify a existing table
3. DROP command to delete a table
Sr. No | Command and description |
1. | CREATE Creates a new table, a view of a table, or other object in the database. Syntax: CREATE TABLE table_name (column_name1 data_type(size), Column_name2 data_type(size), ...... )
Example 1 This example demonstrates how you can create a table named “Account” with four columns. The column names will be “Acc_no” “Acct_hold_name”, “Address”, and “Mobile no”:
Mysql> CREATE TABLE Account (Acc_no int, Acct_hold_name varchar, Address varchar, mobil_no float)
|
2. | ALTER The ALTER command is used to change the structure of a existing table without deleting and recreating it.
Let’s begin with creation of a table called testalter_tbl. Mysql> create table customer (cust_id int, cust_name varchar, cust_addr Varchar (10), acc_no int); Dropping, Adding or Changing size of a Column: Suppose you want to drop an existing column acc_no from above table then DROP clause along with ALTER command is used as follows:
Mysql> ALTER TABLE customer DROP acc_no; A DROP will not work if the mentioned column is the only one left in the table.
To add a column, use ADD and specify the column definition. The following statement Restores the acc_no column to customer table:
Mysql> ALTER TABLE customer ADD COLUMN acc_no int;
Changing a Column Definition or Name: To change a column’s definition, use MODIFY or CHANGE clause along with ALTER command. For example, to change column cust_addr from varchar (10) to Varchar (15), do this:
Mysql> ALTER TABLE customer MODIFY cust_addr varchar (15);
|
3. | DROP Deletes an entire table, a view of a table or other objects in the database. Syntax
DROP TABLE table_name;
For example, if we want to permanently remove the Employee table that we created, we use the following command:
DROP TABLE Employee;
|
Key takeaway:
- DDL queries are used to define and manipulate the structure of tables.
- These commands will primarily be used by database administrators during the setup and removal phases of a database project.
3.1.6 DML (Data Manipulation Language)
DML queries are used to manipulate the data stored in table. Data Manipulation Language commands are used to insert, retrieve and modify the data contained within it.
Sr. No | Command & description |
1. | SELECT Retrieves certain records from one or more tables. Syntax SELECT * FROM tablename.
OR
SELECT columnname, columnname,.. FROM tablename;
|
2. | INSERT Creates a record in tables.
Example 1: Inserting a single row of data into a table Syntax INSERT INTO table_name [(columnname,columnname)] VALUES (expression, expression); To add a new employee to the personal_info table Example INSERT INTO customer values(1,’Ram’,’Pune’,3333444488)
Example 2: Inserting data into a table from another table Syntax INSERT INTO tablename SELECT columnname, columnname FROM tablename
|
3. | UPDATE Modifies record stored in table. The UPDATE command can be used to modify information contained within a table.
Syntax
UPDATE customer SET cust_Address=’Mumbai’ where cust_id=1; |
4. | DELETE Deletes records stored in table. The DELETE command can be used to delete information contained within a table.
Syntax
DELETE FROM tablename WHERE search condition The DELETE command with a WHERE clause can be used to remove his record from The customer table: Example
DELETE FROM customer WHERE cust_id=12; The following command deletes all the rows from the table Example DELETE FROM customer;
|
Key takeaway :
- DML queries are used to manipulate the data stored in table.
- DML commands are used to insert, retrieve and modify the data contained within it.
3.1.7 SQL Operators Tables: Creating, Modifying, Deleting
It is just as important to design and maintain your tables as to ask about them. Creating, editing and deleting (dropping) tables requires designing and handling the tables. Table architecture governs much of the performance of your database and defines the consistency, stability and scalability of data.
Creating tables
The Build TABLE statement is used in a database to create a new table. In that table, use the syntax below if you want to add several columns.
Syntax for creating table
CREATE TABLE table_name (
Column1 datatype,
Column2 datatype,
Column3 datatype,
.....
Column datatype,
PRIMARY KEY (one or more columns)
);
The column parameters define the names of the table's columns.
The parameter of the data type determines the type of data that the column can contain (e.g., varchar, integer, date, etc.).
Create Table example
CREATE TABLE Employee (
EmpId int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Modifying table
In SQL, a SQL ALTER table statement is used to delete, add or even change an existing table's columns. It is often used to implement the table's constraints.
To adjust the data type of a column in a table, we use this order.
The syntax of Alter Table Change SQL Server column-
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
Deleting table
The DELETE statement is used to delete current records from a given record table.
Syntax
DELETE FROM table_name WHERE condition;
Example
DELETE FROM Employee WHERE EmpId=1;
EmpId = 1 record in the Employee table is removed.
Key takeaway:
- Creating, editing and deleting (dropping) tables requires designing and handling the tables.
- Table architecture governs much of the performance of your database and defines the consistency, stability and scalability of data.
3.1.8 Views: Creating, Dropping, Updation using Views
In SQL, a view is a virtual table based on the SQL statement result-set.
A view, much like a real table, includes rows and columns. Fields in a database view are fields from one or more individual database tables.
You can add SQL, WHERE, and JOIN statements to a view and show the details as if the data came from a single table.
Create a view
Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Up-to-date data still displays a view! Any time a user queries a view, the database engine recreates the data, using the view's SQL statement.
Create view example
A view showing all customers from India is provided by the following SQL.
CREATE VIEW [India Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'India';
Updating a view
With the Build OR REPLACE VIEW command, the view can be changed.
Create or replace view syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
For the "City" view, the following SQL adds the "India Customers" column:
CREATE OR REPLACE VIEW [India Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'India';
Dropping a view
With the DROP VIEW instruction, a view is removed.
Drop view syntax
DROP VIEW view_name;
"The following SQL drops the view of "India Customers":
DROP VIEW [India Customers];
Indexes
● Special lookup tables are indexes. It is used to very easily extract data from the database.
● To speed up select queries and where clauses are used, an Index is used. But it displays the data input with statements for insertion and update. Without affecting the data, indexes can be generated or dropped.
● An index is much like an index on the back of a book in a database.
For example, when you refer to all the pages in a book that addresses a certain subject, you must first refer to an index that lists all the subjects alphabetically, and then refer to one or more particular page numbers.
❖ Create index statement
It is used on a table to construct an index. It allows value to be duplicated
Syntax
CREATE INDEX index_name
ON table_name (column1, column2, ...);
Example
CREATE INDEX idx_name
ON Persons (LastName, FirstName);
❖ Unique index statement
It is used on a table to construct a specific index. Duplicate value does not make it.
Syntax
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
Example
CREATE UNIQUE INDEX websites_idx
ON websites (site_name);
❖ Drop index statement
It is used to delete a table's index.
Syntax
DROP INDEX index_name;
Example
DROP INDEX websites_idx;
Key takeaway:
- It is used to very easily extract data from the database.
- To speed up select queries and where clauses are used, an Index is used.
Nulls
A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. Null values are different than zero value.
Syntax
The basic syntax of NULL while creating a table.
SQL> CREATE TABLE CUSTOMERS (
CUST_ID INT NOT NULL primary key,
NAME VARCHAR (20) NOT NULL,
AGE INT,
ADDRESS CHAR (25), salary float
);
Here, NOT NULL signifies that column should always accept a value from user. AGE and ADDRESS columns could be NULL.
Example
The NULL value can cause problems when selecting data. So, to check NULL Value, one must use the IS NULL or IS NOT NULL operators.
Consider the following CUSTOMERS table having the records as shown below.
+----+----------+-----+-----------+----------+
| 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 | |
| 7 | Muffy | 24 | Indore | |
+----+----------+-----+-----------+----------+
Now, following is the usage of the IS NOT NULLoperator.
SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;
This would produce the following result −
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
Now, following is the usage of the IS NULL operator.
SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;
This would produce the following result −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 6 | Komal | 22 | MP | |
| 7 | Muffy | 24 | Indore | |
+----+----------+-----+-----------+----------+
Key takeaway:
- A NULL value in a table is a value in a field that appears to be blank.
- A field with a NULL value is a field with no value
The SELECT statement is used in the database to select / retrieve data from a table. The most frequently used statement is the SQL Pick Statement.
The SELECT Statement question retrieves data from the table as a whole or from some particular columns.
We need to write SELECT statement queries in SQL if we want to retrieve any data from a table.
We retrieve the data by columns in SELECT Statement wise.
Syntax
The SQL SELECT Syntax categorizes into two sections, first we get complete table data from all columns, second we get some unique columns from a table.
SELECT all Columns data from a table
SELECT * FROM table_name;
* = indicate the retrieve all the columns from a table.
Table_name = is the name of table from which the data is retrieved.
SELECT some columns data from a table
SELECT Col1, Col2, Col3 FROM table_name
Col1, Col2, Col3 = is the column name from which data is retrieved.
Table_name = is the name of table from which the data is retrieved.
A SELECT command has number of clauses:
● WHERE clause: Any output row from the FROM stage applies a criterion, further reducing the number of rows.
● GROUP BY clause: Class sets of rows with values balanced by columns in a group.
● HAVING clause: Criteria for each group of rows are added. Criteria can only be extended to columns within a category that have constant values (those in the grouping columns or aggregate functions applied across the group).
● ORDER BY clause: The rows returned from the SELECT stage are sorted as desired. Supports sorting, ascending or descending on multiple columns in a specified order. The output columns will be similar and have the same name as the columns returned from the SELECT point.
● INTO clause: Logically, the INTO clause is last used in processing.
Key takeaway:
- The SELECT statement is used in the database to select / retrieve data from a table.
- We retrieve the data by columns in SELECT Statement wise.
- The SELECT Statement question retrieves data from the table as a whole or from some particular columns.
Mysql> create database set1;
Query OK, 1 row affected (0.00 sec)
Mysql> use set1;
Database changed
CREATING FIRST TABLE:
Mysql> create table A (x int (2), y varchar (2));
Query OK, 0 rows affected (0.19 sec)
Mysql> select * from A;
+------+------+
| x | y |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
CREATING SECOND TABLE:
Mysql> create table B (x int (2), y varchar (2));
Query OK, 0 rows affected (0.22 sec)
Mysql> select * from B;
+------+------+
| x | y |
+------+------+
| 1 | a |
| 3 | c |
+------+------+
2 rows in set (0.00 sec)
❏ INTERSECTION OPERATOR:
Mysql> select A.x, A.y from A join B using (x, y);
+------+------+
| x | y |
+------+------+
| 1 | a |
| 3 | c |
+------+------+
Mysql> select * from A where (x, y) in (select * from B);
+------+------+
| x | y |
+------+------+
| 1 | a |
| 3 | c |
+------+------+
2 rows in set (0.00 sec)
❏ UNION OPERATOR:
Mysql> select * from A union (select * from B);
+------+------+
| x | y |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
Mysql> select * from A union all (select * from B);
+------+------+
| x | y |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 1 | a |
| 3 | c |
+------+------+
6 rows in set (0.00 sec)
❏ DIFFERENCE OPERATOR:
Mysql> select * from A where not exists (select * from B where A.x =B.x and A. y = B.y);
+------+------+
| x | y |
+------+------+
| 2 | b |
| 4 | d |
+------+------+
2 rows in set (0.11 sec)
❏ SYMMETRIC DIFFERENCE OPERATOR:
Mysql> (select * from A where not exists (select * from B where A.x =B.x and A. y=B.y))
Union (select * from B where not exists (select * from A where A.x =B.x and A. y=B.y));
+------+------+
| x | y |
+------+------+
| 2 | b |
| 4 | d |
+------+------+
In the from clause, tuple variables are described using the use of the Only as a clause.
Find the names of your customers and their loan numbers for all Customers at a certain branch have a deposit.
Select customer-name, T. Loan-number, S.amount
From borrower as T, loan as S
Where T.loan-number = S.loan-number
Find the names of all branches with larger assets than those of the branch is in Mumbai.
Select distinct T.branch-name
From branch as T, branch as S
Where T.assets > S.assets and S.branch-city = ‘Mumbai’
Find all branches with greater assets than those branches It's situated in Mumbai.
Select distinct T.branch-name
From branch as T, branch as S
Where T.assets > S.assets and S.branch-city = ‘Mumbai’
Query the same with > any clause
Select branch-name
From branch
Where assets > some
(select assets
From branch
Where branch-city = ‘Mumbai’)
SQL provides the user with some control over the order of the tuples in a relationship. The order by clause causes the tuples in the result of a query to appear in sorted order. To list in alphabetic order all instructors in the Physics department, we write:
Select name
From instructor
Where dept_name = ’Physics’
Order by name;
By default, the order by clause lists items in ascending order. To specify the
Sort order, we may specify desc for descending order or asc for ascending order Furthermore, ordering can be performed on multiple attributes. Suppose that we wish to list the entire instructor relation in descending order of salary. If several instructors have the same salary, we order them in ascending order by name. We express this query in SQL as follows:
Select *
From instructor
Order by salary desc, name asc;
Key takeaway:
- SQL provides the user with some control over the order of the tuples in a relationship.
- To specify the sort order, we may specify desc for descending order or asc for ascending order.
The data that you need is not always stored in the tables. However, you can get it by
Performing the calculations of the stored data when you select it.
Suppose we have table:
Orderdetails |
*orderNumber *productCode QuantityOrdered PriceEach OrderLineNumber |
For example, you cannot get the total amount of each order by simply querying from
The orderdetails table because the orderdetails table stores only quantity and price of each item. You have to select the quantity and price of an item for each order and calculate the order’s total.
To perform such calculations in a query, you use aggregate functions.
By definition, an aggregate function performs a calculation on a set of values and returns a single value.
MySQL provides aggregate functions like: AVG, MIN, MAX, SUM, COUNT.
Suppose we have employees table as shown below:
Mysql> select * from employees;
+------+--------+--------+
| e_id | e_name | salary |
+------+--------+--------+
| 1001 | Sam | 10000 |
| 1002 | Jerry | 11000 |
| 1003 | King | 25000 |
| 1004 | Harry | 50000 |
| 1005 | Tom | 45000 |
| 1006 | Johnny | 75000 |
| 1007 | Andrew | 5000 |
+------+--------+--------+
7 rows in set (0.00 sec)
3.7.1 AVERAGE:
Mysql> Select avg(salary) as “Average Salary” from employees;
+--------------------+
| Average Salary |
+--------------------+
| 31571.428571428572 |
+--------------------+
3.7.2 MINIMUM:
Mysql> Select min(salary) as “Minimum Salary” from employees;
+----------------+
| Minimum Salary |
+----------------+
| 5000 |
+----------------+
3.7.3 MAXIMUM:
Mysql> Select max(salary) as “Maximum Salary” from employees;
+----------------+
| Maximum Salary |
+----------------+
| 75000 |
+----------------+
3.7.4 TOTAL COUNT OF RECORDS IN TABLE:
Mysql>Select count (*) as “Total” from employees;
+-------+
| Total |
+-------+
| 7 |
+-------+
SUM:
Mysql> Select sum(salary) as “Total Salary” from employees;
+--------------+
| Total Salary |
+--------------+
| 221000 |
+--------------+
3.7.5 MATHEMATICAL FUNCTION:
Mysql> select e_id, salary*0.5 as “Half Salary” from employees;
+------+-------------+
| e_id | Half Salary |
+------+-------------+
| 1001 | 5000 |
| 1002 | 5500 |
| 1003 | 12500 |
| 1004 | 25000 |
| 1005 | 22500 |
| 1006 | 37500 |
| 1007 | 2500 |
+------+-------------+
A MySQL subquery is a query nested within another query such as select, insert, update or delete. In addition, a MySQL subquery can be nested inside another subquery.
A MySQL subquery is called an inner query while the query that contains the subquery is called an outer query. A subquery can be used anywhere that expression is used and must be closed in parentheses.
A subquery is also known as a nested query. It is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.
Subqueries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal SELECT statements, but there are few restrictions.
The most important ones are listed below:
● A subquery must always appear within parentheses.
● A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring has only one column. You may use a subquery that returns multiple columns, if the purpose is row comparison.
● You can only use subqueries that return more than one row with multiple value operators, such as the IN or NOT IN operator.
● A subquery cannot be a UNION. Only a single SELECT statement is allowed.
Subqueries are most frequently used with the SELECT statement, however you can use them within an INSERT, UPDATE, or DELETE statement as well, or inside another subquery.
➢ Subqueries with the SELECT Statement
The following statement will return the details of only those customers whose order value in the orders table is more than 5000 dollars. Also note that we’ve used the
Keyword DISTINCT in our subquery to eliminate the duplicate cust_id values from the
Result set.
1. SELECT * FROM customers WHERE cust_id IN (SELECT DISTINCT cust_id
FROM orders WHERE order value > 5000);
➢ Subqueries with the INSERT Statement
Subqueries can also be used with INSERT statements. Here’s an example:
INSERT INTO premium_customers
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders
WHERE order_value > 5000);
The above statement will insert the records of premium customers into a table
Called premium_customers, by using the data returned from subquery. Here the premium customers are the customers who had placed order worth more than 5000 dollars.
➢ Subqueries with the UPDATE Statement
You can also use the subqueries in conjunction with the UPDATE statement to update the single or multiple columns in a table, as follow:
UPDATE orders
SET order_value = order_value + 10
WHERE cust_id IN (SELECT cust_id FROM customers
WHERE postal_code = 75016);
The above statement will update the order value in the orders table for those customers who live in the area whose postal code is 75016, by increasing the current order value by 10 dollars.
Key takeaway:
- MySQL subquery can be nested inside another subquery.
- A subquery is also known as a nested query.
- A subquery can be used anywhere that expression is used and must be closed in parentheses.
3.9.1 Insert
The syntax for the insertion of a new record into a table is quite simple:
INSERT INTO table_name (field1, field2, ...)
VALUES (value1, value2, ...);
Where
● field1 and field2 are fields from table_name.
● Values 1 and 2 are the values for fields 1 and 2, respectively. SQL allows you the flexibility to list the fields in the order you want, as long as the corresponding values are defined accordingly. The following code is thus identical to the question above:
INSERT INTO table_name (field2, field1, ...)
VALUES (value2, value1, ...);
A variant of INSERT makes it possible to insert many comma-separated records at once, as follows:
INSERT INTO table_name (field1, field2, ...)
VALUES (value3, value4, ...),
(value5, value6, ...),
(value7, value8, ...);
3.9.2 Update
To change the data that is already in the database, the SQL UPDATE statement is used. In the WHERE clause, the condition determines which row is to be changed.
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Updating single record
Adjust the EMP NAME column and set the value in the row where SALARY is 500000 to 'Emma'.
Syntax
UPDATE table_name
SET column_name = value
WHERE condition;
Query
UPDATE EMPLOYEE
SET EMP_NAME = 'Emma'
WHERE SALARY = 500000;
Updating multiple record
You can divide each field allocated by a comma if you want to update several columns. Adjust the EMP NAME column to 'Kevin' in the EMPLOYEE table, and CITY to 'Boston' where the EMP ID is 5.
Syntax
UPDATE table_name
SET column_name = value1, column_name2 = value2
WHERE condition;
Query
UPDATE EMPLOYEE
SET EMP_NAME = 'Kevin', City = 'Boston'
WHERE EMP_ID = 5;
3.9.3 Delete
To delete rows from a table, the SQL DELETE statement is used. The DELETE statement usually removes one or more records from a table.
Syntax
DELETE FROM table_name WHERE some_condition;
Deleting single record
Remove the EMPLOYEE table row, where EMP NAME = 'Kristen'. Just the fourth row will be omitted here.
Syntax
DELETE FROM EMPLOYEE
WHERE EMP_NAME = 'Kristen';
Deleting multiple record
Remove the row where AGE is 30 from the EMPLOYEE table. Two rows will be deleted by this (first and third row).
Syntax
DELETE FROM EMPLOYEE WHERE AGE= 30;
A stored procedure is a prepared SQL code that can be saved, so that it is possible to reuse the code over and over again.
So, if you have a SQL query that you write over and over again, save it as a stored procedure and simply call it to run it.
You can also transfer parameters to a stored operation, so that the stored operation can operate on the basis of the value(s) of the parameter transferred.
Syntax
CREATE PROCEDURE procedure_name
AS
Sql_statement
GO;
Execute a stored procedures
EXEC procedure_name;
Parameters make up the most critical component. To transfer values to the Method, parameters are used. There are 3 different parameter categories, which are as follows:
IN:
This is the Process Default Parameter. The values are always retrieved from the calling software.
OUT:
The values are always sent to the calling program by this parameter.
IN OUT:
This parameter accomplishes both operations. This receives value from the calling program as well as sends the values to it.
Key takeaway:
- stored procedure is a prepared SQL code that can be saved, so that it is possible to reuse the code over and over again.
A trigger is a stored database procedure which is automatically invoked if a special event occurs in the database. For instance, when a row is inserted into a specified table or when certain table columns are being modified, a trigger can be invoked.
In fact, triggers are written for execution in response to any of the following events.
● A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
● A database definition (DDL) statement (CREATE, ALTER, or DROP).
● A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
You may identify triggers for the table, display, schema, or database that the event is associated with.
Benefits of Triggers
● Generating some derived column values automatically
● Enforcing referential integrity
● Event logging and storing information on table access
● Auditing
● Synchronous replication of tables
● Imposing security authorizations
● Preventing invalid transactions
Syntax
Create trigger [trigger name]
[before | after]
{insert | update | delete}
On [table_name]
[for each row]
[trigger body]
Key takeaway:
- A trigger is a stored database procedure which is automatically invoked if a special event occurs in the database.
3.12.1 Embedded SQL:
SQL is easier language for writing queries. It is difficult to code the same query using general purpose programming language. However, it is sometimes necessary to have database access from a general-purpose programming language for following reasons: Though SQL is a powerful query language, all queries cannot be expressed in SQL. The reason behind this is that we cannot use general-purpose programming language constructs in SQL. Some queries cannot be expressed in SQL but same can be written in general-purpose programming language like C, Java, or COBOL.
For example, printing of report, sending results of query to GUI, cannot be done using SQL. For an integrated application, the programs written in the programming language must be able to access the database. To write these types of queries in SQL, we can embed SQL within programming language. A language in which SQL queries are embedded is called as a host language, and the SQL structures permitted in the host language constitute embedded SQL. Programs written in the host language can use the embedded SQL syntax to access and update data which is stored in a database. In embedded SQL, all query processing is performed by the database system, which then makes the result of the query available to the program one tuple at a time.
An embedded SQL program must be processed by a special preprocessor prior to the process of compilation. This preprocessor replaces embedded SQL requests with the host- language declarations and procedure calls that allow run-time execution of the database accesses. Then the resulting program is compiled by the host-language compiler. To identify embedded SQL requests to the preprocessor, we use EXEC SQL statement in the following form:
EXEC SQL < embedded SQL statement > END-EXEC
For example, When SQL is embedded within C language; the compiler processes the
Compilation in two steps. It first extracts all the SQL code from the program and the pre- compiler will compile the SQL code for its syntax, correctness and execution path etc.
Once pre-compilation is complete, these executable codes are embedded into the C code. Then the C compiler will compile the code and execute the code. Thus the compilation takes place in two steps – one for SQL and one for application language. Hence these types of compilation require all the query, data value etc to be known at the compilation time itself to generate the executable code. Otherwise C or any high level language cannot compile the code. As SQL code which is written is static and these embedded SQL is also known as static SQL.
Key takeaway:
- SQL is easier language for writing queries.
- It is difficult to code the same query using general purpose programming language.
- An embedded SQL program must be processed by a special preprocessor prior to the process of compilation.
3.12.2 Dynamic SQL
Embedded SQL's biggest drawback is that it only supports static SQLs. If at runtime we need to set up queries, then we can use dynamic sql. That means it's often easier to use dynamic SQL if the query changes based on user feedback. As we mentioned above, the question is different when the user enters the student name alone and the user enters both the student name and the address. If we use embedded SQL, this condition in the code cannot be enforced. In this scenario, dynamic SQL allows the user to construct a query based on the values entered by the user, without letting him know which query is being performed.
It can also be used when we do not know which SQL statements such as Insert, Delete Update or Select need to be used, when the number of host variables is unknown, or when host variable datatypes are unknown, or when DB objects such as tables, views, indexes need to be explicitly referenced.
This can, however, make user specifications clear and convenient, but it will make queries longer and more complicated. That implies that the query can grow or shrink depending on user inputs, making the code versatile enough to handle all the possibilities. The compiler knows the query in embedded SQL beforehand, and the pre-compiler compiles the SQL code long before C compiles the code for execution.
For dynamic SQL, however, queries are only generated, compiled and executed at runtime. This makes the dynamic and time-consuming SQL a little complicated.
Since the query needs to be prepared at runtime, we have three more clauses in dynamic SQL, in addition to the structures addressed in embedded SQL. These are used primarily to construct and execute the query at runtime.
EXECUTE IMMEDIATE
PREPARE and EXECUTE
DESCRIBE
Key takeaway:
- If at runtime we need to set up queries, then we can use dynamic sql.
- it's often easier to use dynamic SQL if the query changes based on user feedback.
3.12.3 ODBC
ODBC stands for Connectivity of the Oracle Database. It is an API that is used for accessing various databases.
The ODBC driver uses Microsoft's Open Database networking platform for interacting with databases. It is separate from databases and from operating systems and platforms. You can run it on other platforms once you create an application using ODBC, with little change in the Data Access Code. ODBC uses SQL syntax and is based on an interface at the Open call level.
The ODBC driver uses Microsoft's Open Database Connectivity (ODBC) interface, which allows applications to access database management system (DBMS) data using SQL as the data access standard. ODBC enables full interoperability, meaning various DBMS can be accessed by a single application. End-users of the application may then add ODBC database drivers to connect the application to their DBMS choices.
The interface of an ODBC driver defines:
● Calls to an ODBC feature library of two types:
● Core features focused on the X/Open and SQL Control Classes
● Specification for Call Level Interface
● Extended features, including scrollable cursors, that support extra features
● SQL syntax, based on the SQL CAE specification of the X/Open and SQL Access Community (1992)
● A common set of codes for errors
● A standard means of linking and logging into a DBMS
● For data types, a regular representation
Fig 2: ODBC
Key takeaway:
- ODBC stands for Connectivity of the Oracle Database.
- It is an API that is used for accessing various databases.
- ODBC uses SQL syntax and is based on an interface at the Open call level.
References:
- Silberschatz A., Korth H., Sudarshan S. “Database System Concepts”, 6th edition, Tata McGraw Hill Publishers
- Elmasri R., Navathe S. “Fundamentals of Database Systems”, 4th edition, Pearson Education, 2003
- Date C. “An Introduction to Database Systems”, 7th edition, Pearson Education, 2002
- Ramkrishna R., Gehrke J. “Database Management Systems”, 3rd edition, McGraw Hill
