1 / 31

SQL

Learn the basics of SQL query language, including creating tables, inserting data, filtering and sorting data, updating and modifying table structure.

btillman
Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL

  2. Introduction to SQL • What is SQL? • When a user wants to get some information from a database file, he can issue a query. • A query is a user–request to retrieve data or information with a certain condition. • SQL is a query language that allows user to specify the conditions. (instead of algorithms)

  3. Introduction to SQL • Concept of SQL • The user specifies a certain condition. • The program will go through all the records in the database file and select those records that satisfy the condition.(searching). • Statistical information of the data. • The result of the query will then be stored in form of a table.

  4. Create table command • This command is use for creating a table. • Rules for creating tables • A name can have maximum upto 30 characters. • A-Z , a-z , 0-9 allowed • A name should be begin with an alphabet • The use of special character like _ allowed and also recommaended • SQL reversed words not allowed.(ex. create , select and so on.)

  5. Create new table • CREATE TABLE TABLENAME (<ATTRIBBUTES> <DATATYPE> <SIZE>); • EX: create table persons(P_ID number(10),LASTNAME varchar2(20),FIRSTNAME varchar2(20),ADDRESS varchar2(20),CITY varchar2(15)); Table Created. Select * from persons;

  6. Now Display all the table • Query : Select * from tab; Display list of tables. Displaying Table Structure • Query : DESC PRSONS;

  7. INSERT DATA INTO TABLE • SYNTAX : INSERT INTO table_nameVALUES (value1, value2, value3,...); • Now we want to insert values in table persons • QUERY INSERT INTO persons values(1,’SHAH’,’CHINTAN’,’SATELLITE’,’AHMEDABAD’); 1 row created

  8. INSERT multiple rows in table • QUERY INSERT INTO persons values(&ID,’&LASTNAME,’&FIRSTNAME’,’&ADDRESS’,’&CITY’); Enter value for ID : 1 . . 1 row created Press / and enter

  9. VIEWING DATA IN THE TABLES • SELECT * FROM PERSONS;

  10. Filtering Table Data • Selected Columns And All Rows • Syntax : SELECT <columnname1>,<columnname2> FROM Table Name ; • Example : SELECT P_ID , LASTNAME FROM PERSONS;

  11. Filtering Table Data • Selected Rows and All Columns • Syntax : SELECT * FROM <Table Name> WHERE <Condition> ; • Example : SELECT * FROM PERSONS WHERE NAME=‘NITIN’;

  12. Distinct • Eliminating duplicate rows • -The DISTINCT clause removing duplicates from the result set. • -The DISTINCT clause can only used with select statements. • -SELECT DISTINCT Syntax • SELECT DISTINCT <COLUMNNAME1> FROM <TABLENAME>;

  13. We use the following SELECT Statement.SELECT DISTINCT city FROM persons;

  14. SORTING DATA IN A TABLE • Orcle allows data from a table to be viewed in a sorted orders. • The rows retrieved from the table will be sorted in either ascending or descending order. • The ORDER BY keyword is used to sort the result-set by specified column. • The ORDER BY keyword sort the records in ascending order by default. • If you want to sort the records in a descending order, you can use the DESC keyword.

  15. SQL ORDER BY SyntaxSELECT column_name(s)FROM table_nameORDER BY column_name(s) ASC|DESC

  16. We use the following SELECT statement:SELECT * FROM personsORDER BY LASTNAME;

  17. ORDER BY DESC exampleSELECT * FROM persons ORDER BY LASTNAME DESC;

  18. DELETE Statement • Removal of all ROWS -Syntax DELETE FROM PERSONS; -All rows deleted

  19. Question: - • If I want to retrieve data of persons table. • What will happen? • What will be an answer?

  20. UPDATING CONTENS OF A TABLE • The UPDATE command is used to change or modify data values in a table. • SQL UPDATE Syntax • UPDATING ALL ROWS UPDATE <TABLENAME> SET <COLUMNNAME1>=<NEW VALUE> , <COLUMNNAME2>=<NEW VALUE>;

  21. Example • Now we want to UPDATE LASTNAME in persons table UPDATE persons SET LASTNAME=‘ABC’;

  22. -Now, what will be effect?-How looks table(persons) UPDATE records Conditionally UPDATE table_nameSET columnname=value, columnname=value2WHERE columnname=value;

  23. Now we want to UPDATE CITY from Ahmedabd to Bombay, whose FIRSTNAME IS CHINTAN • UPDATE Persons • SET CITY=‘BOMBAY’ • WHERE FIRSTNAME=‘CHINTAN’; • What will be result and how looks table?

  24. -What will be result and how looks table?

  25. MODIFY STRUCTURE OF TABLE • ADDING new columns in existing table. • Syntax ALTER TABLE <TABLENAME> ADD(<NEW COLUMNNAME><DATATYPE>(<SIZE>));

  26. Now we want to ADD PINCODE fields in PERSONS table • Query ALTER TABLE persons ADD(PINCODE NUMBER(6)); OUTPUT: -

  27. EXERCISE TIME • NOW INSERT/UPDATE PINCODE IN PERSONS TABLE. • Your table looks like this.

  28. MODIFY STRUCTURE OF TABLE • MODIFY columns in existing table. • Syntax ALTER TABLE <TABLENAME> MODIFY(<COLUMNNAME><NEWDATATYPE> (<NEWSIZE>));

  29. Now we want to MODIFY PINCODE data type from NUMBER TO VARCHAR2 in PERSONS table. • Query ALTER TABLE persons MODIFY(PINCODE VARCHAR2(10)); OUTPUT: - TABLE ALTERED.

  30. RENAMING TABLES • SYNTAX : RENAME <TableName> TO <NewTableName> • EXAMPLE : RENAME PERSONS TO CLIENTS; TABLE RENAMED

  31. TRUNCATING TABLES • EXAMPLE : TRUNCATE TABLE PERSONS; Table Truncatd. DESTROYING TABLES • EXAMPLE: • DROP TABLE PERSONS; • Table Dropped.

More Related