Advertisements
Advertisements
प्रश्न
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उत्तर
SQL Command to add primary key:
ALTER TABLE Employee ADD EmpId INTEGER
PRIMARY KEY;As the primary key is added as the last field, the command for inserting data will be:
INSERT INTO Employee
VALUES("Shweta","Production",26900,999);
OR
INSERT INTO
Employee(EmpId,Ename,Department,Salary)
VALUES(999,"Shweta","Production",26900);APPEARS IN
संबंधित प्रश्न
Each table comprises of ______ and ______.
Which of the following command is used to remove a relation from an SQL database?
To know the names of existing databases, we use the statement:
What statement will you give to view the structure of a table?
To remove an attribute or to add a constraint to an already existing table we use:
Which statement is used to remove a table from the database?
Differentiate between the following statement:
ALTER and UPDATE
A shop called Wonderful Garments which sells school uniforms maintains a database SCHOOLUNIFORM as shown below. It consisted of two relations - UNIFORM and COST. They made UniformCode the primary key for UNIFORM relations. Further, they used UniformCode and Size to be composite keys for COSTrelation. By analyzing the database schema and database state, specify SQL queries to rectify the following anomalies.
- M/S Wonderful Garments also keeps handkerchiefs of red colour, medium size of Rs. 100 each.
- INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100);
When the above query is used to insert data, the values for the handkerchief without entering its details in the UNIFORM relation are entered. Make a provision so that the data can be entered in the COST table only if it is already there in the UNIFORM table. - Further, they should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraints to the SCHOOLUNIFORM database.
- Add the constraint so that the price of an item is always greater than zero.
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.
The code given below reads the following record from the table named student and displays only those records that have marks greater than 75:
- RollNo - integer
- Name - string
- Clas - integer
- Marks - integer
Note the following to establish connectivity between Python and MYSQL:
- Username is root.
- The password is the tiger.
- The table exists in an MYSQL database named school.
Write the following missing statements to complete the code:
Statement 1 - to form the cursor object
Statement 2 - to execute the query that extracts records of those students whose marks are greater than 75.
Statement 3 - to read the complete result of the query (records whose marks are greater than 75) into the object named data, from the table student in the database.
import mysql.connector as mysql
def sql_data():
con1=mysql.connect(host="localhost",user="root",password="tiger", database="school")
mycursor=_______________ #Statement 1
print("Students with marks greater than 75 are :")
_________________________ #Statement 2
data=__________________ #Statement 3
for i in data:
print(i)
print()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.
Write the command to view all databases.
Name any two DDL commands.
Consider the table Personal given below:
| Table: Personal | ||||
| P_ID | Name | Desig | Salary | Allowance |
| P01 | Rohit | Manager | 89000 | 4800 |
| P02 | Kashish | Clerk | NULL | 1600 |
| P03 | Mahesh | Superviser | 48000 | NULL |
| P04 | Salil | Clerk | 31000 | 1900 |
| P05 | Ravina | Superviser | NULL | 2100 |
Based on the given table, write SQL queries for the following:
- Increase the salary by 5% of personals whose allowance is known.
- Display Name and Total Salary (sum of Salary and Allowance) of all personals. The column heading ‘Total Salary’ should also be displayed.
- Delete the record of Supervisors who have salary greater than 25000.
