Advertisements
Advertisements
Question
Differentiate between the following statement:
ALTER and UPDATE
Solution
Alter
- ALTER command is Data Definition Language (DDL).
- ALTER command will perform the action on the structure level and not on the data level.
- ALTER Command is used to add, delete, and modify the attributes of the relations (tables) in the database.
Update
- UPDATE Command is a Data Manipulation Language (DML).
- UPDATE command will perform on the data level.
- UPDATE Command is used to update existing records in a database.
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:
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 is not an RDBMS?
Which of the following are included in defining a schema?
What is not true with respect to the create table statement?
What statement will you give to view the structure of a table?
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?
Raju's date of birth is 21/06/2006. How will his date be added to database?
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.
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.
Write the command to view all tables in a database.
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.
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: 26900Consider 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.
