Class 10 information Technology Code 402 | Unit 3 – RDBMS
SQL Commands in OpenOffice Base
CREATE TABLE Command:
This command is used to create a new table or relation. The syntax for this command is:
CREATE TABLE <table name>
(
<column1> <datatype> [constraint] ,
<column1> <datatype> [constraint],
<column1> <datatype> [constraint],
);
where []=optional
- The keyword CREATE TABLE is followed by the name of the table that you want to create.
- Then within parenthesis, you write the column definition that consists of the column name followed by its data types and optional constraints.
- There can be as many columns as you require.
- Each column definition is separated with a comma (,).
- All SQL statements should end with a semicolon (;).
Example:
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20),
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2),
Date_of_Birth DATE,
Dept_No INTEGER
);
DROP TABLE Command:
This command is used to delete tables.
For example, suppose you want to drop the Teacher table then the command would be:
DROP TABLE Teacher;
ALTER TABLE Command:
This command is used to modify the base table definition. The modifications that can be done using this command are:
Adding a column:
Suppose we want to add a column Age in the Teacher table. The following command is used to add the column:
ALTER TABLE Teacher ADD Age INTEGER;
Dropping a column: A column can be dropped using this command.
ALTER TABLE Teacher DROP Dept_No;
INSERT INTO Command
This command is used to insert a tuple in a relation. We must specify the name of the relation in which the tuple is to be inserted and the values.
- The values must be in the same order as specified during the Create Table command
To insert a tuple in the Teacher table INSERT command can be used as shown below:
INSERT INTO Teacher VALUES (101,”Shanaya”, “Batra”, ‘F’, 50000, ‘1984-08-11’, 1);
Another form of INSERT command is used to insert a tuple in which the ordering of values is done by explicitly specifying the attribute names as shown below:
INSERT INTO Teacher (First_Name, Last_Name, Gender, Teacher_ID, Date_of_Birth, Dept_No, Salary) VALUES (“Shanaya”, “Batra”, ‘F’, 101, ‘1984-08-11’, 1, 50000);
Note that the values entered are ordered with respect to the attributes mentioned.
- If an attribute value is not explicitly specified its DEFAULT value is used.
- If DEFAULT value is also not specified then NULL value is used.
UPDATE Command:
This command is used to update the attribute values of one or more tuples in a table.
For example in the Teacher table, we want to update the Salary of a teacher with Teacher_ID=101 to 55000.
This can be done using the following command:
UPDATE Teacher SET Salary=55000 WHERE Teacher_ID=101;
To increase the salary of a Teacher Shanaya by 5000, the command would be:
UPDATE Teacher SET Salary=Salary+5000 WHERE Teacher_Name=”Shanaya”;
DELETE Command:
In order to delete one or more tuples, the DELETE command is used.
If we want to delete the tuple for Teacher with ID=101 the command would be:
DELETE FROM Teacher WHERE Teacher_ID=101;
To delete all the tuples in a table :
DELETE FROM Teacher;
SELECT Command:
The SELECT Command is used to retrieve information from a database. There are various ways in which the SELECT command can be used.
The syntax of SELECT Command is as follows:
SELECT <attribute list>
FROM <table name>
WHERE <condition>
ORDER BY <column_name> [ASC | DESC] ;
- The attribute list (separated by comma (,)
Use asterisk (*), which means all the attributes.
SELECT * FROM Teacher ;
Display First_Name, Last_Name of all records.
SELECT First_Name, Last_Name FROM Teacher ;
WHERE Clause of SELECT Command
To retrieve all the information about the Teacher with ID=101.
SELECT * FROM Teacher WHERE Teacher_ID=101;
To find the names of all teachers earning more than 50000.
SELECT First_Name,Last_Name FROM Teacher WHERE salary > 50000;
To find the names of all teachers earning more than 50000.
SELECT First_Name,Last_Name FROM Teacher WHERE salary > 50000;
To retrieve names of all the teachers starting from letter ‘S’.
SELECT First_Name FROM Teacher WHERE First_Name LIKE “S%”;
Using Wildcards in Query
Wildcards are symbols that represent a character or combination of characters. The base provides two special wildcards.
* – The asterisk (*), represents a collection of characters.
? – question marks, represents an individual character.
ORDER BY
To sort the result of a query based on some attributes.
This can be achieved by using the clause – ORDER BY followed by the attributes which need to be sorted.
For ascending order the keyword ASC and for descending order the keyword DESC is used. By default, the order is ascending.
To list the names of teachers in alphabetical order.
SELECT First_Name, Last_Name FROM Teacher ORDER BY First_Name, Last_Name;
To list the names of all the Departments in descending order of their names.
SELECT Dept_Name FROM Department ORDER BY Dept_Name DESC;
