Database Sub Queries
Sub Queries
You can include one query as part of another query, this is called a sub query.
The output of a sub query is used inside the main query.
It is also called inner query.
Example
First, the sub query is evaluated and then the result of that sub query will be used to evaluate the main query.
Depending on the scenario, the sub query may return one or more results.
Question 1: What is the name of the customer whose account no is 8?
Answer:
Select customer name from customers where
CID = (select cid from accounts where acc. No. = 8);
Question 2: Display the account number and balance of the customers who are active.
Answer:
Select account no, balance of accounts (In operator is used for more than one record)
Where customer ID in (select C.ID from customers where status = 'enabled');
Question 3: Display the balance of customers who are staying in Pune.
Answer:Select balance of accounts where C. ID in (Select C. ID from address
Where city = 'pune');
Question 4: Display the name and status of the customers who are staying in Pune and Mysore.
Answer:Select customer name, status from customer
Where C.ID in (select city from address Where city in ('pune','mysore','pune');
Question 5: Display the name and email of the customer who has a saving accounts and balance between 5000 and 10000.
Answer:Select customer name, email from customer
where C.ID in (Select atype, balance of accounts where account type ='SA' and balance between 5000 & 10000)
Question 6: How can we create aTable From another Table Using Sub Queries?
Answer:
- With Data:
Create table customer 1 as select from customers; - With Data:
Create table customer 1 as Select C.ID, Customer name, status from customer where Status = 'enabled'; - Without Data:
Create table customer 3 as Select from customers where 1 = 2;
(condition false: empty records Create dummy records here). - With Data:
Create table customer 4 as
Select customer name, email, acc.no. balance
from Customers Cust., accounts acc.,
Where customer C.ID = account C.ID;
Question 7: Display the name and balance of the customers who are staying in Pune.
Answer:
Select customer name, balance from customer cust, accounts acc
where customer C.ID = account C.ID and C.ID in ( Select C.ID from address where City = 'pune');
Alter Command
The alter command modifies the table by adding, deleting or modifying columns in a table which already exists.
Create table student (S.ID int, S.name char (10));Adding the columns : Syntax:
alter table tab_name add (col_name type (size), col_name type (size));
Example:
Alter table students add (email char (15)); Alter table students add fee double,
After student name; (in MYSQL)
Drop Command
The drop command removes a table from the database.
Syntax:
Alter table tab_name drop column col_name;
Example:
Alter table student drop column fee;
Modify Command
The drop command removes a table from the database.
Syntax:
Alter table tab_name modify Col_name type (size);
Example:
Alter table students modify C.ID int;Alter table students modify student name char (2);
-
Adding the Primary Key:
Syntax:
Alter table tablename add primary key Column name;Example:
Alter table tablename add primary key Column name; -
Dropping the Primary Key:
Syntax:
Alter table tab_name drop primary key;Example:
Alter table student drop primary key; -
Adding the constraint:
Syntax:
Alter table tab_name add constraint constraint_name
_ _ _ _ _ _ your constraint here _ _ _ _ _ _;Example:
Alter table student add constraint ck 1, email, not null; -
Adding the constraint:
Syntax:
Alter table tab_name drop Constraint cons_name;Example:
Alter table student drop constraint CK1;