SQL. It education ppt for reference sql process coding

SQL. It education ppt for reference sql process coding

• 1.
  Structured Query Language PreparedBy: Reema Agrawal Asst Professor MERI
• 2.
  SQL  Structured Querylanguage is a language that provides an interface to database systems.  SQL was developed by IBM in 1970.
• 3.
  Features Of SQL: SQL can be used by a range of users, including those with little or no programming experience.  It reduces the amount of time required for creating and maintaining systems.  It is an English like language.
• 4.
  Data types:  SQLdata type is an attribute that specifies type of data of any object.  Each column, variable and expression has related data type in SQL.  These are of several forms and sizes
• 5.
  Data types:  Char-Maximum length of 255 characters. Alphabets only.  Varchar- Maximum of 8,000 characters. Alphanumeric  Int- Numeric and negative  Float- Numeric, decimal and negative integers  Number- It is of floating type. Number (P,S). P- determines the maximum length of the data. S- Number of places to the right of the decimal.
• 6.
  CREATE DATABASE  CreateDATABASE databasename;
• 7.
  The CREATE TABLEcommand  The CREATE TABLE command defines each column of the table uniquely.  Each column has a minimum of three attributes – a name, datatype and size.  Each table column definition is separated from the other by a comma.  Finally, the SQL statement is terminated with a semi colon.
• 8.
  The CREATE TABLEcommand Syntax: CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );
• 9.
  Inserting Data IntoTables  Once a table is created, the most natural thing to do is load this table with data to be manipulated later. Syntax: INSERT INTO <tablename> values (<‘expression 1’>, <‘expression2’>); For specific columns: INSERT INTO <tablename> (<columnname1>, <columnname2>) values (<‘expression 1’>, <‘expression2’>);
• 10.
  Table Name: Employee Emp_IDName Designation Location Salary A10141 Ajay Manager Delhi 75000 A10142 Rahul Executive Delhi 55000 A10143 Shruti Manager Bangalore 80000 A10144 Lokesh VP Bangalore 100000 A10145 Tripti Sales Mumbai 40000
• 11.
  Table Name: Student Student_Rol lNo Name Course Location Total Marks 10126734 Rahul BBA Delhi 500 10117834 Tarun BBA Delhi 586 10128734 Shruti MBA Delhi 540 10118834 Lalit BCA Delhi 523 10126834 Ajay BBA Delhi 596 10118934 Parul MBA Delhi 561
• 12.
  Viewing Data Inthe Tables:  Once the data has been inserted into a table, the next logical operation would be to view what has been inserted.  The SELECT command is used to retrieve rows selected from one or more tables.  To view the whole table  Selected columns and all rows  Selected rows and all columns  Selected columns and selected rows
• 13.
  Viewing Data Inthe Tables:  To view the whole table: Syntax: SELECT * FROM <tablename>; • To view selected columns and all rows Syntax: SELECT <columnname1>, <columnname2> FROM <tablename>;
• 14.
  Viewing Data Inthe Tables:  Selected rows and all columns Syntax: SELECT * FROM <tablename> WHERE <condition>; • Selected rows and selected columns Syntax: SELECT <columnname1>, <columnname2> FROM <Tablename> WHERE <condition> ;
• 15.
  Eliminating Duplicate Rows Specific columns SELECT DISTINCT <columnname1>, <columnname2> from <tablename>;  Entire rows SELECT DISTINCT * from <tablename>;
• 16.
  Sorting Data Ina table  The rows retrieved from the table will be sorted in either ascending or descending order depending on the condition specified in the SELECT sentence. Syntax: Select * from <Tablename> order by <columnname1>; Select * from <Tablename> order by <columnname1>desc;
• 17.
  DELETE OPERATION  TheDELETE in SQL is used to remove either all the rows from a table or a set of rows of a table.  All the rows: Syntax: DELETE FROM <TableName>; • A set of rows from table: Syntax: DELETE FROM <TableName> WHERE <condition>;
• 18.
  ALTER TABLE  Addingnew columns ALTER TABLE <tablename> ADD(<newcolumnname><datatype> (<size>), <newcolumnname><datatype> (<size>));  Dropping column ALTER TABLE <tablename> DROP COLUMN <columnname>;  Modifying columns ALTER TABLE <tablename> MODIFY (<columnname> <Newdatatype>(<Newsize>));
• 19.
  Updating The contentsOf a table:  The UPDATE command is used to change or modify data values in a table.  All the rows: UPDATE <TableName> SET <columnname1>=<expression1>, <columnname2>=<expression2>; • A selected set of rows UPDATE <TableName> SET <columnname1>=<expression1>, <columnname2>=<expression2> where <condition>;
• 20.
  Renaming, Truncating And DroppingTables:  Renaming tables: RENAME <TableName> to <NewTableName>; • Truncating tables: TRUNCATE TABLE <TableName>; • Dropping tables: DROP TABLE <TableName>;
• 21.
  Some Queries:  SELECT* FROM student WHERE name LIKE '%John%‘ (with either first or last name as JOHN)  SELECT * FROM student WHERE name LIKE 'John%‘ (with first name JOHN)  SELECT * FROM student WHERE name LIKE '%John‘ (with last name JOHN)  SELECT * FROM student WHERE name LIKE '%a%' AND name LIKE '%e%‘ (with either first alphabet as a or e)