Sqlfair, Exercises for Computer science. oriani
sujee_sudhakaran
sujee_sudhakaran17 November 2015

Sqlfair, Exercises for Computer science. oriani

DOC (80 KB)
7 pages
610Number of visits
Description
Introduction to Structured Query Language for school students with examples. Concise notes. DIY and understand the working of SQL using ORACLE.
20 points
Download points needed to download
this document
Download the document
Preview3 pages / 7

This is only a preview

3 shown on 7 pages

Download the document

This is only a preview

3 shown on 7 pages

Download the document

This is only a preview

3 shown on 7 pages

Download the document

This is only a preview

3 shown on 7 pages

Download the document

Structured Query Language (SQL) It is a language for interacting with relational databases. It has clearly established

itself as the standard relational database language. SQL allows creating and operating on relational databases. It provides a pre-defined set of commands. The SQL has the following elements - commands, clauses, operators and aggregate functions. These elements can be combined in the form of different statements for the purpose of creating, updating and manipulating databases.

The most Common Datatypes used for specification of various fields of a table or relation to be created are (1) char(n) maximum 240 characters (2) number(n,d) maximum 38 digits, excluding decimal point & sign n- maximum number of digits, d – number of digits to right of decimal point (3) date - The standard ORACLE date format is dd-MON-yy for entering and displaying date. Fixed 7 bytes are used for a DATE column. ORACLE enables to store dates from Jan 1 , 4712 B C to Dec 31, 4712 AD

Data Definition Language (DDL) Commands of SQL DDL-Data Definition Language-is a part of SQL, which helps a user in defining

the data structures in the database. It contains necessary statements for creating, altering and deleting the tables. DDL commands are as follows: 1) CREATE TABLEcommand Use:- A table or relation can be created

Syntax :- CREATE TABLE< relation name> ( < attribute name> <datatype> [constraint],

< attribute name> <datatype> [constraint], ……..);

CREATE , TABLE are the reserved words < relation name> - name of the relation to be created < attribute name> - name of a column or field of the relation constraint - constraint put on the attribute

NULL / NOT NULL - specifies if a column can/cannot have NULL values PRIMARY KEY – defines the column to be the primary key

CHECK – limits values that can be inserted into a column of a table UNIQUE – The values of the column have to be unique

Example:- CREATE TABLE student ( admno number(4) PRIMARY KEY, name char(25), class char(5),marks number(3) ); Result:-Table created 2) ALTER TABLE ….ADD / MODIFY command Use:- The structure of anexisting table can be changed or altered

(i) Syntax :- ALTER TABLE< relation name> ADD ( < attribute name> <datatype> [,……]);

Example:- ALTER TABLE student ADD ( grade char(1));

Result:- Table altered (ii)Syntax :- ALTER TABLE< relation name> MODIFY

( < attribute name> <new datatype> [newconstraint]); Example:- ALTER TABLE student MODIFY ( name char(30));

When this command is executed, the column, name, of the relation student changes its width from 25 to 30.

Result:- Table altered

3) DROP TABLE command Use:- An existing table or relation can be deleted .Its contents are deleted and the table does not exist any more.. Syntax: DROP TABLE <relation name>;

DROP, TABLE are reserved words <relation name> is the name of the relation to be deleted

Example:- DROP TABLE pupil;

Result:- Table dropped 4) DESC (DESCRIBE) command Use:- The structure of a table can be displayed Syntax:- DESC <relation name>; Example:- DESC student;

Result :-Name Null? Type admno Not null number(4)

name char(30) class char(5) marks number(3) grade char(1)

Data Manipulation Language (DML) Commands of SQL DML –Data Manipulation Language-is a part of SQL, which helps in

manipulating the data in the database. It contains statements to retrieve, insert, delete and update the rows of tables of the database. The DML commands are as follows:

1) INSERT command Use:- A row or tuple can be inserted in an existing relation . Syntax:- INSERT INTO <relation name> VALUES ( <col: value1>, <col:val2>,……); Example:- INSERT INTO student VALUES (1234,‘Raj’, ’12C’, 400,’A’); For a particular column, if the data is not known, NULL is to be inserted. Result:- one row created

TABLE:- STUDENT ADMNO NAME CLASS MARKS GRADE

1234 Raj 12C 400 A 1351 Dixon 12E 466 A

1540 Mithun 11D 450 A 1777 George 12D 344 B 1892 Jithin 12E 421 A 1987 Thomas 12C 350 B 2010 Kumar 12A 431 A 2333 Lal 12D 444 A 2456 Sachin 11E 417 A 2568 Ganesh 12D 425 A

2) SELECT DISTINCT Command Use:- To retrieve the data from an existing table .DISTINCT eliminates duplicate rows from a query result. Syntax:- SELECT [DISTINCT] <attrb name>[,< attrb name>,..]

FROM <relation list>; SELECT ,DISTINCT,FROM are reserved words

<attrb name> - name of the column selected from the relation <relation list> - list of relations to be used in SELECT command Example:- SELECT DISTINCT class FROM student; Result:- CLASS 11D

11E 12A 12C 12D 12E

6 rows selected

3) SELECT..FROM….WHERE Command Use:- Select rows from a table that satisfy a particular condition Syntax:- SELECT <attrb name>[,< attrb name>,..]

FROM <relation list> [WHERE <condition>]; SELECT ,FROM,WHERE are reserved words

<attrb name> - name of the column selected from the relation <relation list> - list of relations to be used in SELECT command

<condition> - condition applied to every row of the table/relation. Example:- SELECT admno,name FROM student WHERE grade=’A’; Result:-

ADMNO NAME 1234 Raj 1351 Dixon 1540 Mithun 1892 Jithin 2010 Kumar 2333 Lal 2456 Sachin 2568 Ganesh

8 rows selected

4) SELECT ..FROM…WHERE ..BETWEEN … AND command Use:-The condition BETWEEN … AND can be used to select the rows of a relation falling within a specified range.

Syntax:- SELECT <attrb name>[,< attrb name>,..] FROM <relation list> WHERE <condition>

BETWEEN <start_no> AND <end_no>; SELECT ,FROM,WHERE are reserved words

<attrb name> - name of the column selected from the relation <relation list> - list of relations to be used in SELECT command

<condition> - condition applied to every row of the table/relation. Example:- To select the students from the relation, student, whose rollnumbers lie in the range of 1000 to 2000 SELECT admno,name FROM student WHERE admno BETWEEN 1000 AND 2000;

Result:- ADMNO NAME

1234 Raj 1351 Dixon 1540 Mithun 1777 George 1892 Jithin 1987 Thomas

6 rows selected

5) SELECT ..FROM…WHERE ..< compound condition> command Use:- Using logical operators, many conditions can be combined Syntax:- SELECT <attrb name>[,< attrb name>,..]

FROM <relation list> [WHERE <compound condition>]; SELECT ,FROM,WHERE are reserved words

<attrb name> - name of the column selected from the relation <relation list> - list of relations to be used in SELECT command

<compound condition> - condition applied to every row of the table/relation using logical operators -AND,OR,NOT.

Example: - To list the admission number and name of students getting A grade and of class 12A

SELECT admno,name FROM STUDENT WHERE grade=’A’ AND class=’12A’; Result:-

ADMNO NAME 2010 Kumar

6) SELECT ..FROM…WHERE …LIKE command Use:-LIKE operator is used to match a pattern with the help of the character ‘%’ which matches zero or more characters. Syntax:- SELECT <attrb name>[,< attrb name>,..]

FROM <relation list> WHERE <attrb name> LIKE’pattern’; SELECT ,FROM,WHERE are reserved words

<attrb name> - name of the column selected from the relation <relation list> - list of relations to be used in SELECT command

‘pattern’ refers to character(s) followed by % sign Example:- To display admission number,name,marks of all students

whose name begin with ‘G’ can be displayed by using the pattern ‘G%’ SELECT admno,name,class FROM student WHERE name LIKE’G%’; Result:- ADMNO NAME CLASS

1777 George 12D 2568 Ganesh 12D

7) SELECT ..FROM…WHERE .. ORDER BY command Use:- ORDER BY clause can be used to sort with respect to an attribute in the ascending or descending order SYNTAX:- SELECT <attrb name>,< attrb name>,……

FROM <relationname> WHERE <condition> ORDER BY <attrb name>,<attrb name>……[ASC][DESC];

Example:- SELECT admno,name,class FROM student ORDER BY admno; Result:-

ADMNO NAME CLASS 1234 Raj 12C 1351 Dixon 12E 1540 Mithun 11D 1777 George 12D 1892 Jithin 12E 1987 Thomas 12C 2010 Kumar 12A 2333 Lal 12D 2456 Sachin 11E 2568 Ganesh 12D

8)SELECT..FROM….WHERE IN Command Use:- Select rows from a table that satisfy a particular condition Syntax:- SELECT <attrb name>[,< attrb name>,..]

FROM <relation list> WHERE <attrb name> IN <values>; SELECT ,FROM,WHERE are reserved words

<attrb name> - name of the column selected from the relation <relation list> - list of relations to be used in SELECT command

<condition> - condition applied to every row of the table/relation. Example:- SELECT admno,name FROM student WHERE class IN (‘12D’); Result:- ADMNO NAME

1777 George 2333 Lal 2568 Ganesh

9) SELECT……..GROUP BY command Use:-The rows of a table can be grouped together based on a common value by using the GROUP BY clause of SQL in a SELECT statement. This operation is also called as Categorization

Syntax:- SELECT <attrb name>,< attrb name>,…[Functions] FROM <relationname> GROUP BY <group-by-column>;

Functions- optional entry for aggregate functions <group-by-column> - column for whose same value, the rows in a relation are grouped

Example:- To count the mark group in the relation Student SELECT marks,COUNT(marks) FROM student GROUP BY marks;

Result:- MARKS COUNT(Marks)

344 1 350 1 400 1 417 1 421 1 425 1 431 1 444 1 450 1 466 1

10 rows selected 10) SELECT……..GROUP BY ..HAVING command Use:- A condition is given on the rows grouped on columns with the help of HAVING clause SYNTAX:- SELECT <attrb name>,< attrb name>,…[Functions] FROM <relationname> GROUP BY <group-by-column>

HAVING <condition>; <condition> - It is the condition such that only those groups that satisfy the condition in the HAVING clause are selected

Example:- SELECT class FROM student GROUP BY class HAVING COUNT(class)>40; Result:- no rows selected

11) UPDATE command Use:- If column values of a particular row of a relation have to be changed, UPDATE statement can be used. SYNTAX:- UPDATE <relation name> SET <attrb name>=<new value> WHERE <condition>; SET – reserved word where new values are to be set for column <condition>- condition for the selection of the row where updation is to be done. If condition is not given, all the rows will be modified. Example:- UPDATE student SET admno=admno+100 WHERE class=’12A’; Result:- 1 row updated

12) DELETE command Use:- One or more rows can be deleted from an existing relation satisfying the given condition Syntax:- DELETE <relation name> WHERE <condition>; Example:- DELETE student WHERE admno=1200; Result:- 0 rows deleted

13) CREATE VIEW command Use:- It is used for selecting part of the tables for viewing from the base table.

The derived table is called as the virtual table SYNTAX:- CREATE VIEW <view name> AS

SELECT <attrb name>[,<attrb name>,…] FROM < relation name1>,<relation name2>[,…]

[WHERE <condition>]; < view name> - name of the view to be created

<attrb name> - projected/selected column names < relation name> - relations to be used

Example:- CREATE VIEW view1 AS SELECT name,class,marks FROM student; Result:- View created

comments (0)

no comments were posted

be the one to write the first!

This is only a preview

3 shown on 7 pages

Download the document