Computer Science
For the given table, do as directed:
Table: STUDENT
| ColumnName | Data type | size | Constraint |
|---|---|---|---|
| ROLLNO | Integer | 4 | Primary Key |
| SNAME | Varchar | 25 | Not Null |
| GENDER | Char | 1 | Not Null |
| DOB | Date | Not Null | |
| FEES | Integer | 4 | Not Null |
| HOBBY | Varchar | 15 | Null |
(i) Write SQL query to create the table.
(ii) Write SQL query to increase the size of SNAME to hold 30 characters.
(iii) Write SQL query to remove the column HOBBY.
(iv) Write SQL query to insert a row in the table with any values of your choice that can be accommodated there.
Answer
(i)
CREATE TABLE STUDENT(
ROLLNO INT(4) PRIMARY KEY,
SNAME VARCHAR(25) NOT NULL,
GENDER CHAR(1) NOT NULL,
DOB DATE NOT NULL,
FEES INT(4) NOT NULL,
HOBBY VARCHAR(15)
);
(ii)
ALTER TABLE STUDENT MODIFY SNAME VARCHAR(30);
(iii)
ALTER TABLE STUDENT DROP HOBBY;
(iv)
INSERT INTO STUDENT(ROLLNO, SNAME, GENDER, DOB, FEES, HOBBY)
VALUES (1, 'ANANYA', 'F', '2000-01-01', 5000, 'COOKING');
Related Questions
What is the difference between Candidate key and Alternate key?
What is the degree and cardinality of a table having 10 rows and 5 columns?
Write SQL queries based on the following tables:
Table: PRODUCT
P_ID ProductName Manufacturer Price Discount TP01 Talcum Powder LAK 40 NULL FW05 Face Wash ABC 45 5 BS01 Bath Soap ABC 55 NULL 5H06 Shampoo XYZ 120 10 FW12 Face Wash XYZ 95 NULL Table: CLIENT
C_ID ClientName City P_ID 01 Cosmetic Shop Delhi TP01 02 Total Health Mumbai FW05 03 Live Life Delhi BS01 04 Pretty Woman Delhi 5H06 05 Dreams Delhi FW12 (i) Write SQL Query to display ProductName and Price for all products whose Price is in the range 50 to 150.
(ii) Write SQL Query to display details of products whose manufacturer is either XYZ or ABC.
(iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are not giving any discount.
(iv) Write SQL query to display ProductName and price for all products whose ProductName ends with 'h'.
(v) Write SQL query to display ClientName, City, P_ID and ProductName for all clients whose city is Delhi.
(vi) Which column is used as Foreign Key and name the table where it has been used as Foreign key?
Answer the questions based on the table given below:
Table: HOSPITAL
S.No. Name Age Department Dateofadm Charges Sex 1 Arpit 62 Surgery 1998-01-21 300 M 2 Zareena 22 ENT 1997-12-12 250 F 3 Kareem 32 Orthopaedic 1998-02-19 200 M 4 Arun 12 Surgery 1998-01-11 300 M 5 Zubin 30 ENT 1998-01-12 250 M 6 Ketaki 16 ENT 1998-02-24 250 F 7 Ankit 29 Cardiology 1998-02-20 800 F 8 Zareen 45 Gynaecology 1998-02-22 300 F 9 Kush 19 Cardiology 1998-01-13 800 M 10 Shilpa 23 Nuclear Medicine 1998-02-21 400 F (a) To list the names of all the patients admitted after 1998-01-15.
(b) To list the names of female patients who are in ENT department.
(c) To list the names of all patients with their date of admission in ascending order.
(d) To display Patient's Name, Charges, Age for only female patients.
(e) Find out the output of the following SQL commands:
- SELECT COUNT(DISTINCT Charges) FROM HOSPITAL;
- SELECT MIN(Age) FROM HOSPITAL WHERE Sex = "F";