Topic 08 : SQL-2

67 %
33 %
Information about Topic 08 : SQL-2
Education

Published on October 31, 2013

Author: PradipKharbuja

Source: slideshare.net

SQL - 2 Er. Pradip Kharbuja

What is NULL?  NULL means Unknown or nonexistent.  NULL is not a value.  It is not zero or blank or an “empty string”.  NULL is a special marker used in SQL to indicate that a data value does not exist in the database.  If A and B are NULL, what is the result of A = B?

Data Definition CREATE TABLE departments ( dept_no INTEGER NOT NULL, department_name VARCHAR(30), location VARCHAR(30) PRIMARY KEY (dept_no) );

Primary Key  Unique Value + Not NULL = Primary Key  A primary key cannot allow NULL values.  Each table can have at most one primary key.

Modifying Tables Using SQL  Add an extra column  Drop a column from a table  Modify the maximum length of the table  Add a new constraint like Primary Key, Unique Key  Drop a constraint  Set a default value for a column  Drop a default value for a column

Adding & Removing new column  ALTER TABLE departments ADD department_head VARCHAR(30);  ALTER TABLE departments ALTER COLUMN department_head VARCHAR(50);  ALTER TABLE departments DROP COLUMN department_head;

Data Retrieving  Select  Order By  Aggregate functions  Group by  Subqueries  Joins

SELECT  SELECT * FROM departments;  SELECT * FROM departments WHERE dept_no = 1;  SELECT dept_no, department_name FROM departments;  SELECT department_name FROM departments WHERE dept_no = 2;

ORDER BY  to get result in ascending or descending order.  SELECT * FROM departments ORDER BY dept_no;  SELECT * FROM departments ORDER BY dept_no ASC;  SELECT * FROM departments ORDER BY dept_no DESC;  SELECT department_name, location FROM departments WHERE dept_no = 1 ORDER BY department_name DESC;

workers Table Column Name emp_no first_name last_name job_title age dept_no Type Integer Varchar Varchar varchar Integer Integer Length 30 30 30 NULL Not Null Key Primary Key

workers Table emp_no 1 2 3 4 5 6 7 first_name Lawrence Jason Emily Ahmed Joe Hattie Sally last_name Surani Argo Villa Mukani Todj Smith Boorman job_title Manager Manager Manager Packer Packer Accountant Admin Assistant age 56 33 32 23 24 56 34 dept_no 1 2 3 1 1 2 3

Aggregation Functions 1. Count – returns number of values in a column 2. Sum – returns the sum total of values of a column 3. Avg – returns the mean average of values in column 4. Min – returns the lowest value in a column 5. Max – returns the highest value in a column

Aggregation Functions  SELECT COUNT(emp_no) FROM workers;  SELECT COUNT(emp_no) AS number_of_workers FROM workers;  SELECT SUM(age) FROM workers;  SELECT AVG(age) FROM workers;  SELECT MAX(age) FROM workers;  SELECT MIN(age) FROM workers;

GROUP BY  Display dept_no and count the number of workers in that department.  SELECT dept_no, COUNT(emp_no) FROM workers;  This query will produce an error.  SELECT dept_no, COUNT(emp_no) FROM workers GROUP BY dept_no;

GROUP BY  SELECT dept_no, COUNT(dept_no) FROM workers WHERE dept_no >=2 GROUP BY dept_no;  SELECT dept_no, COUNT(dept_no) FROM workers WHERE dept_no >=2 GROUP BY dept_no ORDER BY dept_no DESC;  Display dept_no which has more than 2 workers.  SELECT dept_no FROM workers GROUP BY dept_no HAVING COUNT(emp_no) > 2;

Sub Queries  A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause.  Subqueries must be enclosed within parentheses.  A subquery can have only one column in the SELECT clause  Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator

Sub Queries  Display the department_name of the depeartment having the highest dept_no.  SELECT department_name FROM departments WHERE dept_no = (SELECT MAX(dept_no) FROM departments);

Joins  An SQL JOIN clause is used to combine rows from two or more tables, based on condition.  Types of Join 1. 2. 3. 4. 5. Cross Join or Cartesian Product Inner Join Left Join Right Join Full Join

Cartesian Join or Cross Join  Concept is like Cartesian Product.  eg. A = {1, 2, 3}, B = {a, b}  Cartesian Product will be : A x B = {(1, a), (1, b), (2, a), (2, b), (3, a), (3, b)}

Cartesian Join or Cross Join  SELECT * FROM departments, workers;

Inner Join  SELECT * FROM departments, workers WHERE departments.dept_no = workers.dept_no;

Key clauses in an SQL SELECT statement  SELECT – specifies which columns from the table are to appear in the result  FROM – specifies which table or tables are to be used to get the results  WHERE – specifies some condition that will restrict the rows that are retrieved  GROUP BY – groups rows by some column value  HAVING – used to restrict the result that will be grouped  ORDER BY – specifies the order in which the result will appear

Query Optimisation  Making sure a query runs as efficiently and as quickly as possible  The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans.  eg. TOAD  Available at http://www.quest.com/toad/

ANY QUESTIONS? SQL - 2

Slides available @ http://www.pradipkharbuja.com.np/softwarica/database

Add a comment

Related presentations

Related pages

AD Gruppen SQL 2008 R2 - MS SQL Server Forum - MCSEboard ...

Apr 2016 Windows Server 2008 R2, MS SQL : 2 Antworten 168 Aufrufe; lizenzdoc 01. ... 08. Mär 2016 Windows Server Forum & IT Pro Forum ...
Read more

SQL2K8 - Backup Fehler 64 - MS SQL Server Forum ...

Geschrieben 08. August 2012 - 14:21. Steht doch in der Fehlermeldung: ... Apr 2016 Windows Server 2008 R2, MS SQL : 2 Antworten 171 Aufrufe; lizenzdoc
Read more

SQL Fundamentals - Practice 08 - Documents

SQL Fundamentals - Practice 08. by abby-ar. on Nov 18, 2014. ... Topic 08 : SQL - 2 Sql xp 08 1. Implementing Views and BatchesObjectivesIn this lesson, ...
Read more

Database | Pradip Kharbuja

Topic 08: SQL – 2. Topic 09: Database Design. Topic 10: Supporting Transactions. Topic 11: Implementation. Download the past questions, marking schemes ...
Read more

Access 2010 - Unterformular - Recordsource - Google Groups

Me.Recordsource = sql.... 2. Funktionsaufruf über das Hauptformular ... On 20 Sep., 08:53, "Henry Habermacher" wrote:
Read more

SQL: 2 Zeilen zu einer zusammenfassen - administrator.de

Apple Entwicklung Hardware Internet Linux Microsoft Multimedia Netzwerke Off Topic ... SQL: 2 Zeilen zu einer ... (Level 1) - Jetzt verbinden. 14.08.2014 ...
Read more

[MySQL] 2 Tabellen vergleichen... - ABAKUS

08.04.2016, 10:23 : mySQL: Ist die Anzahl der Spalten imm... klaraSand: Webprogrammierung, Templatedesign & Microformats: 11: 04.12.2015, 15:28 : mySQL ...
Read more

Tabelle via SQL-Befehl von Datenbank 1 zu Datenbank 2 ...

Verfasst: Do 11.08.11 08:59 . Danke Nersgatt, das Thema hat sich mit der bitteren Wahrheit somit erledigt. MfG Shorti. Singlepin Beiträge: 36 Erhaltene ...
Read more