CLASS 10 SESSION - 6 SQL (DATABASE MANAGEMENT SYSTEM)

SESSION -6

 SQL(STRUCTURED QUERY LANGUAGE)

SQL means Structured Query Language. SQL is used to communicate with a relational database.

In the database you can define the structure of the date and manipulate the data using some commands. 

These commands are grouped into four different categorises.

1. DDL (Data Definition Language)- Data Definition Language is used to define the structure of a database.

these are some DDL commands-

CREATING TABLE USING SQL

CREATE- It creates the database or its objects (like table, index, function, views, store procedure and triggers)

Syntex- CREATE TABLE TABLE NAME(COLUMN 1 NAME    DATA TYPE(SIZE), COLUMN 2 NAME    DATA TYPE(SIZE), COLUMN 3 NAME    DATA TYPE(SIZE), COLUMN 4 NAME    DATA TYPE(SIZE), COLUMN 5 NAME    DATA TYPE(SIZE), COLUMN 6 NAME    DATA TYPE(SIZE), COLUMN 7 NAME    DATA TYPE(SIZE))

 EXAMPLE - CREATE TABLE STUDENTS (ADMNO INTEGER, NAME CHAR(20), GENDER CHAR(1), CLASS CHAR(5), MARKS INTEGER)    

STEP-1 Open Base.

Step 2- Click on Tool option and select SQL option.

Step 3- Type Command in Command Box.

Step 4- Click on Execute button to run the command.

Step 5- Click on View Menu and select Refresh Tables option.

Step 6- Then you can see a table "STUDENT" is ready in Tables.

Step 6- Click on table "STUDENTS". Then you can see table is ready with columns (ADMNO, NAME, GENDER, CLASS AND MARKS)

ADDING ONE MORE COLUMN IN TABLE

ALTER- It is used to alter the structure of the database. This command is used to add, modify or delete columns in an existing table. 

Add one more column in given table "STUDENTS" as ADDRESS.

SYNTAX-                 ALTER TABLE TABLE NAME

                                  ADD COLUMNNAME DATA TYPE

COMMAND-         ALTER TABLE STUDENTS

                               ADD ADDRESS CHAR(20)

STEP 1- Again open SQL command box. Then type command and click on Execute button.

Step 2- Go to View Menu and click on Refresh Table option. Then open Table "STUDENTS" and see one New Column "ADDRESS" is added.

DELETING ONE COLUMN IN TABLE

Delete one column in given table "STUDENTS" as CLASS.

DROP is used to delete the objects from database.

Syntax- ALTER TABLE TABLE NAME

              DROP COLUMN COLUMN NAME

EXAMPLE-  ALTER TABLE STUDENTS

                     DROP CLASS

STEP-1 Again type commands in Command box and click on Execute button.

STEP 2- SEE one column "CLASS" has been deleted.

2. DML (Data Manipulation Language)-   

DML includes commands to enable users to enter and manipulate data. 

INSERT - INSERT statement is used to add one or more records into a table.

INSERT A RECORD IN THE PREVIOUS TABLE "STUDENTS"

Syntex- INSERT INTO STUDENTS (COLUMN 1, COLUMN 2..................................)

              VALUES(VALUE 1, VALUE 2,.........................................................................)

EXAMPLE- 

INSERT INTO STUDENTS ("ADMNO","NAME","GENDER","CLASS","MARKS", "ADDRESS")

VALUES ('1001','MANU','M','XA','98','SAROJININAGAR')

STEP 1- Enter the command. Then click on Execute button.

STEP 2- SEE THE CHANGES. One Record about MANU is added in the Table "STUDENTS".

UPDATE - Update statement is used to edit or modify the value of a column in the table.

 

CHANGE MANU WITH ANKIT.

EXAMPLE- UPDATE STUDENTS

                      SET NAME='ANKIT'

                      WHERE ADMNO='1001'

STEP 1- Enter the commands. Then click on Execute.

Step 2- See the change. Now in place of Manu, Ankit is updated.

DELETE- Delete statement is used to remove one or more records in a table or database.

Delete the records of Ankit from the table STUDENTS.

STEP 1- Enter the commands. Click on Execute.

STEP 2- See the output below. The record of Ankit has been deleted.

CREATE QUERY WITH SQL VIEW

1. SELECT- SELECT statement is used to select from one or more database tables. SELECT is most commonly used DML command. 

WHERE clause-    it specifies criteria that restricts the result table.

FROM clause- It specifies the tables and views from which data is to be read.

ORDER By clause- It allows to specify the columns on which the result table is to be sorted.

(a) Display all the details of Male students.

SELECT * FROM STUDENTS WHERE GENDER='M'

STEP 1- Select Queries Option. Then click on 'Create query with SQL view' option.

STEP 2- Type Command. Then Run Query option or Press F5 key to run the query. Then in the output, you can see only Male students.

(b) Display the name and address of the students whose marks is more than 80.

(c) DISPLAY THE NAMES WITH MARKS THOSE WHO ARE GETTING BETWEEN 80 TO 100.

(D) Display the names of the students in ascending order.

(e) Display the detail of the students in ascending order.

(f) Display the address of the student whose ADMNO is 1004.

(G) DISPLAY THE NAMES OF STUDENTS WITH INCREED MARKS BY 5 IN ALL SUBJECTS.

.....................................................................................................................................................

LOOK THE TABLE AND WRITE SQL COMMAND-

1. TO INSERT A NULL VALUE- 

INSERT INTO STUDENTS VALUES(106,'MADHU VERMA','FEMALE',NULL,57)

2. To display the gender of the student whose marks is 80.

      SELECT GENDER FROM STUDENTS WHERE MARKS=80

3. To display all information of males. 

       SELECT * FROM STUDENTS WHERE GENDER='MALE'

4. Display THE DETAILS OF STUDENTS THOSE WHO ARE GETTING MORE THAN 50 MARKS.

       

       SELECT * FROM STUDENTS WHERE MARKS > 50

4. Display THE DETAILS OF STUDENTS THOSE WHO ARE GETTING MARKS BETWEEN 50 TO 70.

SELECT * FROM STUDENTS WHERE MARKS BETWEEN 50 AND 70

5. 

FORWARD THIS POST TO NEEDY STUDENTS AS MORE AS POSSIBLE. 

 More for Practice

SOLVE THESE IN COMPUTER LAB-