1) Explain why it\'s a good idea to have DROP TABLE statements at the beginning

Question

1) Explain why it's a good idea to have DROP TABLE statements at the beginning of your DDL SQL script files that are used to create one or more tables. 2) How can you drop a table from your database that has one or more other tables referencing it with foreign keys? 3) What is a HAVING clause used for and how does this differ from a WHERE clause? 4) Give an example of how a subquery can be used in the WHERE clause of a SELECT statement. 5) Give your own example of an SQL statement containing one of the following commands: CREATE, DELETE, UPDATE, SELECT, INSERT

Explanation / Answer

1.)  It's a good idea to have DROP TABLE statements at the beginning of your DDL SQL script files that are used to create one or more tables. It is because there might be a possibility that a table with same name already ecxist.In that case your CREATE TABLE script will fail. To avoid that scenerio so that we dont have a clash and query doesnot fails.

2.)You have to first drop the foreign key constrainin using

DROP Constraint-Name

Then only you will be able to drop the table

Drop table table-name;

3) The HAVING clause was added to database because the WHERE keyword could not be used with aggregate functions.

Aggregate functions are like "count", "Sum" ,"max","min"

The WHERE clause is used to extract only those records that fulfill a specified criterion.So it directly compares properties of coulmn elemnsts only.

eg:-

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

4) Subquery is a query nested within another query.

When we need where clause on something which itself needs to be filteres based on certain condition we need subquery.

Example:- suppose we need to select details Of cities from a table based on All the countries from a particular continent.

SO the query can be:-

Select city_id, city_name,pin_code,population,state from City where country in ( Select Country from Country_list where continent ='South America');

Assumptions:-

Table1= City with columns "city_id" , "city_name" , "pin_code", "state" ,"population","country"

Table 2=Country_list with columns "Country" , "continent" , "country_id"

5)

Referencing using same table:-

Table1= City with columns "city_id" , "city_name" , "pin_code", "state" ,"population","country"

Table 2=Country_list  with columns "Country" , "continent" , "country_id"

Select * from City;

Select * from City where state='Arizona';

Delete from Country_list where continent='Persia';

Update City set pin_code=109867 where city_name='san diego';

----------------------------------------------------

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

----------------------------------------------

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

--------------------------------------------------

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.