Unit - 3
Introduction to SQL - PL/SQL
Q.1) What do you mean by nested query?
Ans: Nested Query
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.
Q.2) Define trigger, write the benefits also?
Ans: Trigger
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]
Q.3) Write short notes on ODBC?
Ans: ODBC
ODBC stands for Connectivity of the Oracle Database. It is an API that is used for accessing various databases.
The OFBC 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
Q.4) What is the set operation?
Ans: Set Operations
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 |
+------+------+
Q.5) What is the SQL? write characteristics and advantages of SQL?
Ans: 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 me for database management systems used for relational database management. It is open-source, Oracle Company - supported database program me. 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.
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.
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.
Q.6) What is the data type of SQL?
Ans: Data types of SQL
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.
- 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. |
Q.7) What do you mean by DDL?
Ans: 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 an existing table
3. DROP command to delete a table
S. 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 an 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;
|
Q.8) Define dynamic SQL?
Ans: 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
Q.9) What is the stored procedures?
Ans: Stored procedures
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 procedure
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 me by this parameter.
IN OUT:
This parameter accomplishes both operations. This receives value from the calling program me as well as sends the values to it.
Q.10) Write the select query and clauses for SQL?
Ans: Select query
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.
Clauses
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.
Unit - 3
Introduction to SQL - PL/SQL
Q.1) What do you mean by nested query?
Ans: Nested Query
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.
Q.2) Define trigger, write the benefits also?
Ans: Trigger
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]
Q.3) Write short notes on ODBC?
Ans: ODBC
ODBC stands for Connectivity of the Oracle Database. It is an API that is used for accessing various databases.
The OFBC 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
Q.4) What is the set operation?
Ans: Set Operations
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 |
+------+------+
Q.5) What is the SQL? write characteristics and advantages of SQL?
Ans: 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 me for database management systems used for relational database management. It is open-source, Oracle Company - supported database program me. 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.
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.
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.
Q.6) What is the data type of SQL?
Ans: Data types of SQL
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.
- 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. |
Q.7) What do you mean by DDL?
Ans: 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 an existing table
3. DROP command to delete a table
S. 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 an 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;
|
Q.8) Define dynamic SQL?
Ans: 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
Q.9) What is the stored procedures?
Ans: Stored procedures
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 procedure
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 me by this parameter.
IN OUT:
This parameter accomplishes both operations. This receives value from the calling program me as well as sends the values to it.
Q.10) Write the select query and clauses for SQL?
Ans: Select query
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.
Clauses
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.
