Reference no: EM133271869

Activity: Check Your Progress

Instruction: Provide the correct SQL commands

1. Create a table Course.

2. Create a table Student.

3. Write SQL Queries to drop address column in student tables.

4. Write SQL Queries to add email and mobile columns in student tables.

5. Add the following data into C_code, C_name and duration fields of Course table
C_code C_name Duration
UG001 BCA 3
UG002 B Sc-IT 3
PG003 M Sc-IT 2

6. Add five (5) records to Student table.

7. John created a table in Mysql. Later, he found that there should have been another column in the table. Which command should he use to add another column to the table?

Question 1.
CREATE TABLE Course(
C_code varchar(15),
C_Name varchar(15),
Duration int
);

Question 2.
CREATE TABLE Student(

Stud_ID varchar(15),
Name varchar(15),
Address varchar(50),
C_Code varchar(15)
);

Question 3.
ALTER TABLE student DROP COLUMN Address;

Question 4.
ALTER TABLE Student ADD (Email varchar(20), Mobile char(14));

Question 5.

INSERT INTO course (C_code, C_Name, Duration) VALUES
('UG001', 'BCA','3'),
('UG002','B Sc-IT','3'),
('PG003', 'M Sc-IT', '2')

Question 6.
INSERT INTO student(Stud_ID, Name, C_Code, Email, Mobile) VALUES

('001', 'Ram','YS4', '[email protected]', '09090767543'),
('002', 'Jass','RE5', '[email protected]', '09078699076'),
('003', 'Jess','PT9', '[email protected]', '09568899008'),
('004', 'Kiss','CC1', '[email protected]', '09067899990'),
('005', 'Jazz','CC4', 'jazz785gmail.com', '09077788999')

Question 7. use the ALTER TABLE ADD command. This command alters a table and adds in a column with the specified data type. Using this command, you can add a column to a table after you have created the table. We can use the ALTER TABLE statement to alter our existing table and add in this new column.
Example:

Alter TABLE tablename ADD fieldname datatype(size);

ALTER TABLE Student ADD (Mobile char(14));
or
ALTER TABLE student ADD Mobile CHAR(250);

While creating a table 'Customer' Aaron forgot to set the primary key for the table. Give the statement which she should write now to set the column 'CustiD' as the primary key of the table?

Peter has already created a table ‘Hospital' as shown below. Now she wants to add a new column ‘Address' to the given table.

While creating a table named "Employee", Mr. Rishi got confused as which data type he should chose for the column "EName". Help him in choosing the right data type to store employee name. Give valid justification for the same.

Consider the table RESULT given below. Write command to insert a new row 6, "Mohan", 500, "English", 73, "Second"

Rewrite the following SQL statement after correcting error(s). Underline the corrections made.

INSERT IN STUDENT (RNO, MARKS) VALUE (5,78.5);

In a database there are two tables "Product" as shown below. Write the command to increase the Price of all the Products by 20.

Question 8. ALTER TABLE Customer ADD PRIMARY KEY (CustiD);

Question 9. ALTER TABLE Hospital ADD Address varchar(25);

Question 10. Varchar would be the suitable data type for EName column as char data type is a fixed length data type while varchar is a variable length data type. Any employee's name will be of variable length so it's advisable to choose varchar over char data type.

Question 11. INSERT INTO Result VALUES (6,"Mohan"' 500, "English", 73, "SECOND");

Question 12. INSERT IN STUDENT (RNO, MARKS) VALUE (5,78.5);

Question 13. UPDATE PRODUCT SET PRICE=PRICE + 20;

Question 14. UPDATE GYM SET BRANDNAME="Fit Trend India" where ICODE="G101";

Question 15. a. Write command to change the color of garment with code as 116 to "Orange".

b. Write command to increase the price of all XL garments by 10%

c. Write command to delete the record with GCode "116"