Unit 3 - SQL Commands

 CBSE Revision Notes

Class-11 Computer Science (New Syllabus)
Unit 3: Data Management (DM-1)


SQL Commands

CREATE TABLETables 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_codeProduct_NameSupp_code
123Jeans321
124Shirts322
125Tshirts333

Supplier table

Supp_codeSupp_name
321DENIM
322Adidas
333Reebok
334Jockey

SELECT product.product_name, Supplier.Supp_name FROM product, supplier WHERE product.supp_code=supplier.Supp_code;

Output of this query will be

Product_nameSupp_name
JeansDENIM
ShirtsAdidas
TshirtsReebok

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 (column1column2..);

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)