Answer:
Various DML (Data Manipulation Language) commands
- SELECT used to select different columns
- INSERT to put the data into tables
- UPDATE to modify the data
- DELETE to delete the data.
1. SELECT Command
SELECT command consists of expressions and strings. In the general form of basic SQL query, the select-list consists of:
- Expressions and
- Column name
SELECT expression AS column name Where
Expression: It refers to mathematical or string expression, which is specified on column names and constants.
Column Name: It refers to the column’s new name in the resultant query. It can also include aggregate functions/operators such as SUM, AVG, COUNT, MIN, MAX etc. It also allows the use of standard ready to use functions such as sqrt, mod etc.
Querys
Query (a): Find the names of all the employees who are working for “operation” department.
Solution:
SELECT E.ename
FROM Employee E, Department D
WHERE E.DNo = D.DNo AND
D.Dname = “operation”. The answer is “David”
Query (b): Calculate the increment in the salary of the employees who are working on two different projects carried out at the same location.
Solution:
SELECT E.ename, E.esal + 1000 As salary
FROM Employee, Department Dl, Department D2
WHERE D1 .Dept_managerid = E.eid AND
D1 .Dlocation = D2.Dlocation AND
Dl.PNO <> D2.PNO
This will result in a new relation when ‘esaF coTumn is renamed as ‘salary’ by using AS
Result: Use of ‘AS’ clause
Ename Salary
David 000
Sam 000
With select command we can select specific columns by specifying the names of the columns. But by using ‘*’ with select command all the columns can be selected at once (no need to mention the names of all the columns).
Query (c): List all the information about the employees whose salary is greater than or equal to 20000.
Solution:
SELECT *
FROM Employee E
WHERE E.esal > = 20000.
The use of ‘select *’ is useful for those queries which are interactive but it is a poor style as it does not clearly mention the schema of the resulting relation. To determine the schema one has to refer the relation mentioned in FROM clause.
Result of “Select *”
| Eid | Ename | DNo | Esal | Age | Phone |
| 101 | John | 2 | 35000 | 50 | 24578912 |
| 100 | Henry | 7 | 22000 | 25 | 55809192 |
| 97 | David | 5 | 30000 | 41 | 23535135 |
| 108 | Sam | 1 | 25000 | 32 | 24532121 |
| 102 | Henry | 2 | 22000 | 35 | 24578290 |
| 120 | Smith | 4 | 20000 | 20 | 56408489 |
When strings are sorted alphabetically then we can apply comparison operators.
Collation
It is a mechanism that is used to compare the string characters to determine which characters are smaller (ASCII code) than the other characters in a particular string.
In addition to this, SQL provides another operator LIKE operator to perform pattern matching. It is of the form,
Scalar expression LIKE literal [Escape character] where,
Scalar expression = string value
Literal = ‘-’ single character
= ‘%’ zero or more character sequence!
The string ‘% ar %’ results the set of strings which contains the characters ‘ar’ in them. The length of the resulting string must be atleast four characters.
To understand this, consider the following query
Query (d): List the names of the employees whose name start with ‘H’ and has ‘r’ as the third character.
Solution:
SELECT E.ename AS name, E.sal as salary
FROM Employee E
WHERE E.ename LIKE ‘H-r%’.
This will result in a relation consisting of names of all the employees whose name start with H and third character is ‘r’. The answer in this case is
Name Salary
Harry 18000
Query (e): Find all the employees whose department name starts with ‘pac’.
Solution:
SELECT *
FROM Employee E, Department D
WHERE E.eid = D.Dept_Managerid AND
D.Dname LIKE ‘pac %’
2. Insert Command
The insert statement is used to insert or add a row of data into the table.
To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.
insert into “tablename”
(first_column,…last_column)
values (first_value,…last_value);
In the example below, the column name first will match up with the value ‘Luke’, and the column name state will match up with the value ‘Georgia’.
Example: insert into employee
(first, last, age, address, city, state)
values (‘Luke’, ‘Duke’, 45, ‘2130 Boars Nest’,
‘Hazard Co’, ‘Georgia’);
3. Update Command
Update Rows
The syntax for this command is
update tablename set colname1=colvalue where colname2=colvalue;
Example: update Student set Name = ‘Ajay’ where id = 2;
This command has updated the Name ‘Rose’ in Student table whose id is 2.
4. Delete Command
Delete Rows
The syntax for this command is-
delete from tablename where [search_conditions];
Example: delete from Student where id=1;
This statement is used to delete the row from Student table where the student id is 1.
Total Views: 16
