1 / 181

learn sql server online tutorial | TechandMate

Learn SQL Server online tutorial, which provides a descriptive learning of the Database concepts along with the working of the relational databases.<br>

TechandMate
Download Presentation

learn sql server online tutorial | TechandMate

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. Introduction to Structured Query Language (SQL) Learn SQL Server With US By Techandmate.com

  2. Objectives • Explore basic commands and functions of SQL • How to use SQL for data administration (to create tables, indexes, and views) • How to use SQL for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information

  3. Agenda • What is SQL ? • Data Types  • Constraints • Database Relationships • SQL Queries  • Transact- SQL Commands | DDL , DCL ,DML • Retrieving Data • Customizing Data • Grouping Data • SQL Operators  • Joining Data

  4. Agenda continue • Inserting , Updating and Deleting Data • Working With Tables • Working with Views • Working With Constraints  • Generating Scripts  • Working with Stored Procedures • Working  with Functions

  5. Introduction to SQL • SQL stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems • SQL functions fit into two broad categories: • Data definition language • SQL includes commands to: • Create database objects, such as tables, indexes, and views • Define access rights to those database objects • Data manipulation language • Includes commands to insert, update, delete, and retrieve data within database tables

  6. SQL Database Objects • A SQL Server database has lot of objects like • Tables • Views • Stored Procedures • Functions • Rules • Defaults • Cursors • Triggers

  7. SQL Server Data types • Integer : Stores whole number • Float : Stores real numbers • Text : Stores characters • Decimal: Stores real numbers • Money : Stores monetary data. Supports 4 places after decimal • Date : Stores date and time • Binary : Stores images and other large objects • Miscellaneous : Different types special to SQL Server.

  8. Not null Constraints Database Constraints • A null value is an unknown • Null value is not as zero or space.

  9. Unique Constraints • every value in a column or set of columns must be unique. • When thereis not the primary key. • Example • no two employees can have the same phone number

  10. Primary key • The primary key value must be unique and not null. • Multiple UNIQUE constraints and only one Primary key in a Table .

  11. Foreign key • FOREIGN KEY in one table points to a PRIMARY KEY in another table. • prevents that invalid data form being inserted into the foreign key column

  12. Check Constraints • used to limit the value range that can be placed in a column. • Example : • A column must only include integers greater than 0

  13. DEFAULT Constraint • used to insert a default value into a column

  14. Transact-SQL Commands • Data Definition Language (DDL) • Create , Alter , Drop , Truncate • meant to deal with the structure of the database objects (the object itself) like tables, views, procedures and so on. • Data Manipulation Language (DML) • Insert , Delete , Update , SELECT • deal with the contents of the tables rather than the structure of the tables • Data Control Language (DCL) • GRANT , DENY , REVOKE • maintain security of the database objects access and use

  15. Data Definition Language (DDL) • CREATE • adding a new database object to the database • ALTER • changing the structure of an existing database object • DROP • removing a database object from the database permanently • TRUNCATE • removing all rows from a table without logging the individual row deletions

  16. Data Manipulation Language (DML) • SELECT • retrieving data from the database by specifying which columns and rows to be retrieved • INSERT • adding a new row (and not column) into the table • UPDATE • modifying the existing data in the tabl • DELETE • removing an existing data from the table

  17. Data Control Language (DCL) • GRANT • giving privileges to the users • DENY • denies permissions from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships • REVOKE • removing privileges from the users that are previously granted those permissions

  18. SELECT • Definition • Retrieve data from database • Syntax SELECT * | column1 [, column2, …….] FROM table

  19. Example

  20. WHERE • Definition • Specify a condition to limit the result • Syntax SELECT * | column1 [, column2, …….] FROM table [WHERE conditions]

  21. Example

  22. IS Null Function

  23. Order BY • Definition • sort the retrieved rows by a specific column or set of columns • Syntax SELECT * | column1 [, column2, …….] FROM table [WHERE conditions] [ORDER BY column1 [, column2, ……..] ASC, DESC]

  24. Example

  25. Examples : Sort Descending 1 2

  26. Concatenate

  27. Aliases (as)

  28. Distinct • eliminates duplicate row values from the results • Example : the Next Slide

  29. Example: Distinct

  30. Grouping Data

  31. Grouping Functions

  32. Example 1: SUM

  33. Example 2 : SUM

  34. Group By • Definition • used to divide the rows in a table into smaller groups • Syntax SELECT * | column1, group_function (column2), ……. FROM table [WHERE conditions] [GROUPBY column1, ……..] [ORDER BY column1 [, column2, ……..] ASC, DESC]

  35. Example : Group By

  36. Having • Definition • used to restrict the group results • Syntax SELECT * | column1, group_function (column2), ……. FROM table [WHERE conditions] [GROUPBY column1, ……..] HAVING [conditions] [ORDERBY column1 [, column2, ……..] ASC, DESC]

  37. Example : Having

  38. SQL Operators

  39. Using SQL Operators • Arithmetic operators • Comparison operators • Logical operators • Set Operators • Other operators

  40. Arithmetic operators • addition (+) • subtraction (-) • multiplication (*) • division (/).

  41. Comparison operators • compare two or more values

  42. Example

  43. Logical operators • Used to get a logical value (True or False)

  44. Example

  45. Set Operators • Definition • combine the results of two or more queries into one result • Keywords • UNION/ UNION ALL • INTERSECT • EXCEPT

  46. UNION & Intersect & Except

  47. Union Operator • Create a Single Result Set from Multiple Queries • Each Query Must Have: • Similar data types • Same number of columns • Same column order in select list USE northwind SELECT(firstname + ' ' + lastname)ASname,city, postalcode FROM employees UNION SELECT companyname, city, postalcode FROM customers GO

  48. INTERSECT Operator • Definition • returns only the records that have the same values in the selected columns in both tables. • Syntax USE northwind SELECT(firstname + ' ' + lastname)ASname,city, postalcode FROM employees INTERSECT SELECT companyname, city, postalcode FROM customers GO

  49. EXCEPT Operator • Definition • return rows returned by the first query that are not present in the second query • Syntax USE northwind SELECT(firstname + ' ' + lastname)ASname,city, postalcode FROM employees EXCEPT SELECT companyname, city, postalcode FROM customers GO

  50. Other operators

More Related