Advertisements
Advertisements
Question
Navdeep creates a table RESULT with a set of records to maintain the marks secured by students in Sem1, Sem2, Sem3, and their divisions. After the creation of the table, he entered data of 7 students in the table.
| ROLL_NO | SNAME | SEM1 | SEM2 | SEM3 | DIVISION |
| 101 | KARAN | 366 | 410 | 402 | I |
| 102 | NAMAN | 300 | 350 | 325 | I |
| 103 | ISHA | 400 | 410 | 415 | I |
| 104 | RENU | 350 | 357 | 415 | I |
| 105 | ARPIT | 100 | 75 | 178 | IV |
| 106 | SABINA | 100 | 205 | 217 | II |
| 107 | NEELAM | 470 | 450 | 471 | I |
Based on the data given above answer the following questions:
- Identify the most appropriate column, which can be considered as the Primary key.
- If two columns are added and 2 rows are deleted from the table result, what will be the new degree and cardinality of the above table?
- Write the statements to:
a. Insert the following record into the table
Roll No - 108, Name - Aadit, Sem1- 470, Sem2 - 444, Sem3 - 475, Div - I.
b. Increase the SEM2 marks of the students 3% whose name begins with ‘N’.
OR
Write the statements to:
a. Delete the record of students securing IV division.
b. Add a column REMARKS in the table with datatype as varchar with 50 characters.
Advertisements
Solution
- ROLL_NO
- New Degree: 8
New Cardinality: 5 - a. INSERT INTO RESULT VALUES (108, ‘Aadit’, 470, 444, 475, ‘I’);
b. UPDATE RESULT SET SEM2=SEM2+ (SEM2*0.03) WHERE SNAME LIKE “N%”;
OR
a. DELETE FROM RESULT WHERE DIV=’IV’;
b. ALTER TABLE RESULT ADD (REMARKS VARCHAR(50));
APPEARS IN
RELATED QUESTIONS
Each table comprises of ______ and ______.
Case Based
Consider the table STUDENT with the following detail.
| STU_ID | NAME | STREAM | MARKS | CLASS |
| 1. | Aditya | Science | 87.5 | 12A |
| 2. | Vikram | Commerce | 88.7 | 12B |
| 3. | Astha | Humanities | 76.8 | 12C |
| 4. | Varsha | Science | 79.5 | 12A |
| 5. | Kanishka | Science | 77.9 | 12A |
| 6. | Anand | Commerce | 86.7 | 12B |
Now answer the question given below:
Command to select all Science students from the table STUDENT
Which of the following command is used to remove a relation from an SQL database?
A tuple in RDBMS is referred to as ______ of a table.
To know the names of existing databases, we use the statement:
What is not true with respect to the create table statement?
How will you add a primary key to a relation?
How will you add two columns coll and col2(composite key) as primary key to a relation?
How will you add a foreign key to a relation?
Which statement is used to remove a table from the database?
Differentiate between the following statement:
ALTER and UPDATE
Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:
- Add a new column Discount in the INVENTORY table.
- Set appropriate discount values for all cars keeping in mind the following:
(i) No discount is available on the LXI model.
(ii) VXI model gives a 10 percent discount.
(iii) A 12 percent discount is given on cars other than the LXI model and VXI model. - Display the name of the costliest car with the fuel type “Petrol”.
- Calculate the average discount and total discount available on Baleno cars.
- List the total number of cars having no discount.
______ command is used to remove the primary key from a table in SQL.
Which of the following commands will delete the table from the MYSQL database?
______ is a non-key attribute, whose values are derived from the primary key of some other table.
To establish a connection between Python and SQL databases, connect() is used. Which of the following arguments may not necessarily be given while calling connect()?
Write the command to view all tables in a database.
Write the command to view all databases.
Name any two DDL commands.
The code given below deletes the record from the table employee, which contains the following record structure:
E_code - StringE_name - StringSal - IntegerCity - String
Note the following to establish connectivity between Python and MySQL:
- Username is
root - Password is
root - The table exists in a MySQL database named
emp. - The details
(E_code, E_name, Sal, City)are the attributes of the table.
Write the following statements to complete the code:
Statement 1 – to import the desired library.
Statement 2 – to execute the command that deletes the record with E_code as 'E101'.
Statement 3 – to delete the record permanently from the database. ____________
import ____________ as mysql #Statement1
def delete():
mydb=mysql.connect(host="localhost",use r="root",
passwd="root",database="emp")
mycursor=mydb.cursor()
____________ #Statement 2
____________ #Statement 3
print ("Record deleted") Ms Shalini has just created a table named “Employee” containing columns Ename, Department and Salary. After creating the table, she realized that she has forgotten to add a primary key column in the table. Help her in writing an SQL command to add a primary key column EmpId of integer type to the table Employee.
Thereafter, write the command to insert the following record in the table:
EmpId- 999
Ename- Shweta
Department: Production
Salary: 26900