Question

2. Using to Figure 6-11 page 283 of your text book (Modern Database Management, 11th Edition

Author:	Jeffrey A. Hoffer ; Ramesh Venkataraman ; Heikki Topi
VBID:	9781323027226

, write SQL data definition commands for each of the following queries:

a) How would you add an attribute, Class, to the Student table?
b) How would you remove the Registration table?
c) How would you change the FacultyName field from25 characters to 40 characters?

Modern Database Management, 11th Edition

Author:	Jeffrey A. Hoffer ; Ramesh Venkataraman ; Heikki Topi
VBID:	9781323027226

3. Using to Figure 6-11 page 283 of your textbook, write SQL commands for the following:

Modern Database Management, 11th Edition

Author:	Jeffrey A. Hoffer ; Ramesh Venkataraman ; Heikki Topi
VBID:	9781323027226

Figure 6-11

a) Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table.
b) Now write a command that will remove Lopez from the Student table.
c) Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases.

4. Define each of the following terms:

a) dynamic SQL
b) correlated subquery
c) embedded SQL
d) procedure
e) join

Answer 1

a) How would you add an attribute, Class, to the Student table?

Alter table Student Add Class varchar(20);

b) How would you remove the Registration table?

Drop table Registration;

c) How would you change the FacultyName field from25 characters to 40 characters?

Alter table table_name Modify FacultyName varchar(40);

a) Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table.

first form: INSERT INTO table_name VALUES('65798','Lopez'); // this form can create problems if columns sequence mismatch.

Second Form: INSERT INTO table_name(ID,Last_Name) VALUES('65798','Lopez'); // It will store id and last name in appropriate values.

b) Now write a command that will remove Lopez from the Student table.

Delete From Student where Last_name='Lopez';

c) Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases.

Update Table_name SET Name='Introduction to Relational Databases' where Course='ISM 4212'; // Name and Course are column Name.

a) dynamic SQL

Dynamic Structure query Language allows you to write SQL statements dynamically means at run time. Sometimes Static SQL is not possible to provide desired results. Static SQL means when you know everything like database definations, references prior to execution at compile time, But sometimes SQL statements depends on user inputs and those inputs known at run time only. So there dynamic SQL comes in picture.

b) correlated subquery

Correlated subquery is a nested syncronized query which uses value of outer Query.The subquery is evaluated once for each row processed by the outer query.

SELECT emp_no
   FROM employees e
   WHERE No_of_orders > (
     SELECT AVG(No_of_orders)
       FROM employees
       WHERE department = e.department);

Here Outer Query is

SELECT emp_no
   FROM employees e
   WHERE No_of_orders >

Here Inner Query is

SELECT AVG(No_of_orders)
       FROM employees
       WHERE department = e.department);

In the above nested query the inner query has to be re-executed for each employee.

c) embedded SQL

Embeded SQL is SQL statements embeded in program Source code of host language. The host language cannot parse SQL, the inserted SQL is parsed by an embedded SQL preprocessor. The C programming language is commonly used for embedded SQL implementation. So you need to start with a host language, writing your code in the normal way. When you get to a point where a database operation needs to be carried out, use embedded SQL statements to carry them out — the host code "calls" SQL statements. You can pass data between the host and SQL: SQL-data values go to variables in the host code. example

SELECT SALE_AMOUNT FROM TOTAL_SALES WHERE AGENT_NO=xx

Here xx is normal host language code variable which user expects to input.

procedure

Procedure in SQL is the set of logical group of SQL statements which are grouped to perform a specific task. There are many benefits of using a stored procedure like performance of the database. Example

Create Procedure Procedure-name 
(
Input parameters ,
Output Parameters (Optional)
)
As
Begin
     Sql statement used in the stored procedure
End

Procedure is different from Functions in a way that Procedure do not directly return any value and function return one value.

Join

Joins in SQL is used to combine records of more than One Table. It is combining fields of two table by using values common to each.

Example : CUSTOMERS

ID | NAME     | AGE | ADDRESS   | SALARY   |
----------------------------------------------
| 1  abc         22     India       50000

   2      dss                    77          UK                    45000

ORDERS

OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 301      23-9-2009             1           3232

  302       23-9-2010             5           3332

SQL Statement for Join is

SELECT ID, NAME, AGE, AMOUNT
        FROM CUSTOMERS, ORDERS
        WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Result.

ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
| 1   abc         22    3232        // Because only one record match between two tables.

Types of Joins are:

Self Join   is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement

Cartesian Join   returns the Cartesian product of the sets of records from the two or more joined tables.

FULL JOIN   returns rows when there is a match in one of the tables.

RIGHT JOIN   returns all rows from the right table, even if there are no matches in the left table.

LEFT JOIN   returns all rows from the left table, even if there are no matches in the right table.

Inner Join returns rows when there is a match in both tables.