GENERAL INSTRUCTIONS Notebook 80120 pages single line No

GENERAL INSTRUCTIONS • Notebook: : 80/120 pages single line • No need to make Index. • Fill all your details on the cover of the notebook & on the first page with a permanent marker. • Leave first page • Notes to be written in only Blue/Black ink/ball/gel pen • To underline or emphasize use Pencil / Red ink/ball/gel pen

What is database? A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose. What is DBMS? It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications. What are the advantages of DBMS? 1. 2. 3. 4. 5. Redundancy is controlled. Unauthorized access is restricted. Providing multiple user interfaces. Enforcing integrity constraints. Providing backup and recovery.

SQL Introduction: SQL stands for “Structured Query Language” and can be pronounced as “SQL” or “sequel – (Structured English Query Language)”. It is a query language used for accessing and modifying information in the database. IBM first developed SQL in 1970 s. Also it is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the RDBMS. SQL consist of data definition language (DDL) and data manipulation language (DML). SQL DATA DEFINATION LANGUAGE DATA MANIPULATION LANGUAGE

Data Definition Language : It is used to create, alter, drop or delete new database tables. The selected keywords which are used to perform the particular task are as under: CREATE TABLE - creates a new table ALTER TABLE - alters a database table DROP TABLE - deletes a database table Data Manipulation Language : It is used to getting, insert, delete and update data in database and tables. The selected keywords which are used to perform the particular task are as under: SELECT - displays data from table UPDATE - updates data in table DELETE - deletes data from table INSERT INTO - inserts new data into table

How to start SQL on Computer: • • Open My Computer, click on C drive icon. Select and Open folder named “instaclient” Click on the file named “connect. bat” After this you will get a interface as shown below: SQ Pro L mp t

CREATE TABLE Statement: This statement is used to create tables to store data. Syntax: CREATE TABLE table_name (column_name 1 datatype, column_name 2 datatype, . . . column_name. N datatype ); • • • table_name - is the name of the table. column_name 1, column_name 2. . - is the name of the columns datatype - is the datatype for the column like char, date, number etc. Example: To create a table of the name student_Rno with different fields. CREATE TABLE student _Roll. Number (roll_no number(4), name char(20), CREATE TABLE house char(10), EMP_Roll. Number class number(2), (E_ID number(4), section char(1)); E_NAME char(20), SALARY number(4),

To view the table structure: DESCRIBE <TABLE NAME> To clear the sql screen: CLEAR SCREEN To close the sql screen: QUIT To view the all the tables: SELECT * FROM TAB; SQL RENAME : The SQL RENAME command is used to change the name of the table or a database object. Syntax: RENAME old_table_name To new_table_name; Example: To change the name of the table student to my_class RENAME student_Roll. Number TO my_class. Roll. Number;

Database Table Tuple Attribute : Data is a collection of facts, such as values or measurements. It can be numbers, words, measurements, observations or even just descriptions of things : It is basically a computer based record keeping system. It is also defined as a collection of interrelated data stored together to serve multiple applications. : A place where data is arranged in rows and columns. : The rows of table(s) are referred to as tuples. : The columns of table(s) are generally referred to as attributes. Datatypes : : Datatypes are means to identify the type of data and associated operations of handling it. varchar(n) : Variable-length character string. The maximum size for n is 2000 (4000 in Oracle 8). Only the bytes used for a string require storage. Example: varchar 2(80) Number(o) : Numeric data type for integers, o = overall number of digits, Maximum values: o=38 Examples: number(3) Date : Date data type for storing date and time. The default format for a date is: DD-MMM-YY. Examples: ’ 13 -OCT-94’, ’ 07 -JAN-98’

INSERT : The INSERT Statement is used to add new rows of data to a table. Syntax: INSERT INTO TABLE_NAME [ (col 1, col 2, . . . )] VALUES (value 1, value 2, . . . ); • col 1, col 2, . . . col. N -- the names of the columns in the table. Example: If you want to insert a row to the student table, the query would be like, INSERT INTO student (roll_no, name, house, class, section) VALUES (2051, 'Srinath', ‘Ajmer', 10, ’C’); SELECT: The SQL SELECT statement is used to retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. Syntax: SELECT column_list FROM table-name [WHERE Clause] • table-name is the name of the table from which the information is retrieved. • column_list includes one or more columns from which data is retrieved. Example: 1. SELECT * FROM Employee; 2. SELECT Id, Name, Salary FROM Employee;

ALTER TABLE: is used to modify the structure of a table by modifying the definition of its columns. It is used to perform the following functions: : 1) Add, drop, modify table columns 2) Add and drop constraints 3) Enable and Disable constraints To add a column Syntax: : ALTER TABLE table_name ADD column_name datatype; Example: : To add a column “hobby" to the student table ALTER TABLE student ADD hobby number(3); To drop a column Syntax: : ALTER TABLE table_name DROP column_name; Example: : To drop the column “hobby" from the student table ALTER TABLE student DROP hobby; To modify a column Syntax: : ALTER TABLE table_name MODIFY column_name datatype; Example: : To modify the column “name” in the student table ALTER TABLE student MODIFY name char(25);

DELETE: The SQL DELETE statement is used to remove records from a table in the database. Syntax: DELETE FROM table-name [WHERE Clause] • table-name is the name of the table from which the information is retrieved. Example : 1. DELETE FROM Employee where salary>=40000; (It remove the records where salary i>=40000) 1. DELETE FROM Employee; (It removes all the records. ) RDBMS(Relational Database Management System): A DBMS in which data is stored in tables and the relationships among the data are also stored in tables. Example: My. SQL, SQL Server, Access, Oracle, Sybase, DB 2, etc.

ASSIGNMEN T

STUD_REC_ SCHNO STUDENT_NAME CLASS SUBJECT PERCENTAGE 76 RAM X MATH 90 83 SIMMI IX ENGLISH 75 173 KAPIL IX SCIENCE 85 234 PARESH VIII HINDI 80 54 KARAN IX ENGLISH 95 89 KEN X MATH 92 122 SUGREEV VIII COMPUTER 90 134 SALIM VIII SCIENCE 70

Write the sql statement: i) To create a table STUD_REC_ with the above given attributes. Ans: CREATE TABLE STUD_REC_ (SCHNO NUMBER(4), STUDENT_NAME CHAR(20), CLASS CHAR(4), SUBJECT CHAR(15), PERCENTAGE NUMBER(3)); ii) To insert a new record with the following values: 165, ‘Tarak’, ‘VIII’, ‘Coreldraw’ , 80 Ans: INSERT INTO STUD_REC_ VALUES (165, ‘TARAK’, ‘VIII’, ‘CORELDRAW’ , 80); iii) To display all the records of the table in ascending order of names. Ans: SELECT * FROM STUD_REC_ ORDER BY STUDENT_NAME; iv) To display the details of students who are from class X. Ans: SELECT * FROM STUD_REC_ WHERE CLASS=‘X’; v) To display the details of students who are of class IX & are having subject science Ans: SELECT * FROM STUD_REC_ WHERE CLASS=‘IX’ AND SUBJECT=‘SCIENCE’;

Write the output for SQL queries vii) to xi). vii) SELECT SUBJECT, SCHNO FROM STUD_REC_; viii) SELECT SUBJECT FROM STUD_REC_; ix) SELECT SCHNO, STUDENT_NAME FROM STUD_REC_ WHERE SUBJECT=‘MATH’ x) SELECT CLASS FROM STUD_REC_ WHERE PERCENTAGE > 85; xi) SELECT SCHNO, CLASS, PERCENTAGE FROMSTUD_REC_ WHERE CLASS=’VIII’ AND PERCENTAGE <=90 ORDER BY PERCENTAGE;

TO VIEW THE STRUCTURE OF THE TABLE DESCRIBE STUD_REC_; TO VIEW ALL THE RECORDS WITH THE TABLE SELECT * FROM STUD_REC_; TO INPUT NEW RECORD TO THE TABLE INSERT INTO STUD_REC_ VALUES (…, …, …); TO SAVE THE RECORDS ENTERED COMMIT;

UPDATE STATEMENT: It is used to modify the data in a table UPDATE TABLE_NAME SET COLUMN_NAME = NEW_VALUE WHERE COLUMN_NAME = SOME_ VALUE Example: Update one Column in a Row We want to change the student name to Raman Verma where schno is 165 UPDATE STUD_REC_ SET STUDENT_NAME = 'Raman DELETE STATEMENT is used to delete rows in a table Verma' WHERE SCHNO : =It 165; DELETE FROM TABLE_NAME WHERE COLUMN_NAME = SOME_ VALUE Example: Delete one Row DELETE FROM STUD_REC_ SET WHERE SCHNO = 165; Delete all Row DELETE FROM STUD_REC_ ; OR DELETE * FROM

ALTER TABLE: It is used to ADD or DROP columns in an existing table. ALTER TABLE TALBE_NAME ADD COLUMN_NAME DATATYPE; ALTER TABLE STUD_REC_ ADD GRADE CHAR(1); ALTER TABLE TALBE_NAME DROP COLUMN_NAME; ALTER TABLE STUD_REC_ DROP COLUMN GRADE; ALTER TABLE TALBE_NAME MODIFY COLUMN_NAME; ALTER TABLE STUD_REC_ MODIFY CLASS CHAR(5);

Function Syntax: The syntax for built-in SQL functions is: SELECT function(column) FROM table_name; Aggregate functions: operate against a collection of values, but return a single value. Function Description • AVG(column) Returns the average value of a column • COUNT(*) Returns the number of selected rows • MAX(column) Returns the highest value of a column • MIN(column) Returns the lowest value of a column • SUM(column) Returns the total sum of a column • COUNT(column) Returns the number of rows (without a NULL value) of a column

• To display number of records in stud_rec_ table: SELECT count(*) FROM stud_rec_; • To display maximum percentage obtained: SELECT Max(percentage) FROM stud_rec_; • To display minimum percentage obtained: SELECT Min(percentage) FROM stud_rec_; • To display sum of all percentage obtained: SELECT Sum(percentage) FROM stud_rec_; • To display average of all percentage obtained: SELECT Avg(percentage) FROM stud_rec_;

SQL Scalar functions: SQL scalar functions return a single value, based on the input value. Some useful scalar functions are: • UCASE() - Converts a field to upper case • LCASE() - Converts a field to lower case • MID() - Extract characters from a text field • LEN() - Returns the length of a text field • ROUND() - Rounds a numeric field to the number of decimals specified • NOW() - Returns the current system date and time

SQL Wildcard Characters: A wildcard character can be used to substitute for any other character(s) in a string. In SQL, wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table. With SQL, the wildcards are: Wildcard Description % A substitute for zero or more characters _ A substitute for a single character [charlist] Sets and ranges of characters to match [^charlist] Matches only a character NOT specified or within the brackets [!charlist]

SELECT * FROM stud_rec_ WHERE subject LIKE ‘%AT%'; SELECT * FROM stud_rec_ WHERE subject LIKE ‘_ATH'; Using the SQL [charlist] Wildcard: The following SQL statement selects all students with subject starting with “A", “M", or “S": SELECT * FROM stud_rec_ WHERE subject LIKE '[AMS]%'; The following SQL statement selects all students with a subject NOT starting with “A", “M", or “S": SELECT * FROM stud_rec_ WHERE subject LIKE '[!AMS]%';

SQL NULL Values If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value. • • NULL values represent missing unknown data. By default, a table column can hold NULL values are treated differently from other values. NULL is used as a placeholder for unknown or inapplicable values. Note: It is not possible to compare NULL and 0; they are not equivalent.

To select only the records with NULL values in the “grade" column, we will have to use the IS NULL operator: SELECT * FROM stud_rec_ WHERE grade IS NULL To select only the records with NO NULL values in the “grade" column, we will have to use the IS NOT NULL operator: SELECT * FROM stud_rec_ WHERE grade IS NOT NULL