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.
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:
Create table customer 1 as select from customers;
Create table customer 1 as Select C.ID, Customer name, status from customer where Status = 'enabled';
Create table customer 3 as Select from customers where 1 = 2; 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');
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)
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;
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);Syntax:
Alter table tablename add primary key Column name;
Example:
Alter table tablename add primary key Column name;
Syntax:
Alter table tab_name drop primary key;
Example:
Alter table student drop primary key;
Syntax:
Alter table tab_name add constraint constraint_name
_ _ _ _ _ _ your constraint here _ _ _ _ _ _;
Example:
Alter table student add constraint ck 1, email, not null;
Syntax:
Alter table tab_name drop Constraint cons_name;
Example:
Alter table student drop constraint CK1;