Unit 3 - SQL Commands
CBSE Revision Notes
This topic is common in class 11 computer sicence and informatics practices.
SQL Commands
CREATE TABLE: Tables are defined with the create table command. Create table command basically defines a table name describing a set of named columns in a specified order. It also defines data type and size of the columns. Each table must have 1 column.
Syntax for create table command is as follows
CREATE TABLE Products
(
Product_code int PRIMARY KEY,
Product_Name varchar(10) NOT NULL,
Supp_CODE int NOT NULL
);
In the table product product_code is of type integer and it’s a primary key. Product_name is of type varchar which means it can hold characters and NOT NULL means it’s value cannot be NULL. Supp_CODE is of type integer.
CREATE TABLE Supplier
(
Supp_CODE int PRIMARY KEY,
Sup_Name varchar(10) NOT NULL,
FOREIGN KEY (Supp_CODE) REFERENCES Products (Supp_CODE)
);
Here we are creating a table called supplier here Supp_code is primary key and it’s also a foreign key.
INSERT: The INSERT INTO statement is used to insert new records in a table. It’s syntax is as follows
INSERT INTO table_name VALUES (value1, Value2, Value3, ….);
INSERT INTO product VALUES (1234,”Jeans”, 54321);
This line will add values in table called product and will put value in their respective columns.
DELETE: The DELETE statement is used to delete existing records in a table. Syntax of DELETE command is as follows.
DELETE FROM table-name where condition;
DELETE FROM product where product_code=1234;
This will delete the record whose product code is 1234
DELETE FROM product where Product_name=’Jeans”;
This command will delete all the records whose product name is jeans.
If we’ll omit where clause
DELETE FROM product;
It will delete all the records in the table
DROP TABLE: We can also delete the structure of the table completely by drop command
DROP TABLE product;
It will delete the table itself.
NOTE: DELETE command only deletes the records and DROP command deletes whole table.
SELECT : SELECT command retrieves data from the table. This means that by executing SELECT statement information currently in table will be shown on the screen. Syntax of SELECT command
SELECT * FROM product;
This will display all the records from product table
SELECT Product_name FROM product;
This will display all the product names from product table
SELECT product_name, sup_code FROM product WHERE product_code=1234
It will display the name and supplier code whose product code is 1234.
JOINS: A JOIN clause is used to combine rows from two or more tables, based on a related column between them. This can be done using SELECT command. Let’s understand this with an example suppose we have 2 table’s products and supplier
Product table
| Product_code | Product_Name | Supp_code |
| 123 | Jeans | 321 |
| 124 | Shirts | 322 |
| 125 | Tshirts | 333 |
Supplier table
| Supp_code | Supp_name |
| 321 | DENIM |
| 322 | Adidas |
| 333 | Reebok |
| 334 | Jockey |
SELECT product.product_name, Supplier.Supp_name FROM product, supplier WHERE product.supp_code=supplier.Supp_code;
Output of this query will be
| Product_name | Supp_name |
| Jeans | DENIM |
| Shirts | Adidas |
| Tshirts | Reebok |
This command will select product name from product table and supplier table and will display those records where sup_code of both the records are matching.
INDEX: The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. Updating a table with indexes takes more time than updating a table without indexes because indexes also need an update. So create indexes on only those columns that will be frequently searched against.
Syntax to create indexes are as follows.
CREATE INDEX index_name ON table_name (column1, column2..);
This command creates and indexes and duplicates are allowed in this.
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
This command creates indexes which are unique and does not allow duplicates.
CREATE INDEX lname ON person(last_name)
