Advertisements
Advertisements
प्रश्न
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.
Advertisements
उत्तर
- UPDATE Personal
SET Salary=Salary*1.05
WHERE Allowance IS NOT NULL; - SELECT Name, Salary+Allowance AS
"Total Salary" FROM Personal; - DELETE FROM Personal
WHERE Salary>25000
APPEARS IN
संबंधित प्रश्न
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:
Delete records that belongs to 'Humanities' stream.
Which of the following command is used to remove a relation from an SQL database?
Which of the following are included in defining a schema?
To know the names of existing databases, we use the statement:
What is not true with respect to the create table 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:
How will you add two columns coll and col2(composite key) as primary key to a relation?
Which statement is used to remove a table from the database?
Raju's date of birth is 21/06/2006. How will his date be added to 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()?
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.
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