SQL

SQL

SQL SELECT Statement

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or 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. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY 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.
• The code within the brackets is optional.

database table student_details;

id	first_name	last_name	age	subject	games
100	Manoj	Bhadana	10	Science	Cricket
101	Sanjeev	Singh	12	Maths	Football
102	Suraj	Kumar	09	Science	Cricket
103	Shekar	Gowda	18	Maths	Badminton
104	Priya	Chandra	15	Economics	Chess

NOTE: These database tables are used here for better explanation of SQL commands. In reality, the tables can have different columns and different data. 
For example, consider the table student_details. To select the first name of all the students the query would be like: 

SELECT first_name FROM student_details;

NOTE: The commands are not case sensitive. The above SELECT statement can also be written as "select first_name from students_details;" 
You can also retrieve data from more than one column. For example, to select first name and last name of all the students. 

SELECT first_name, last_name FROM student_details;

You can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement. We will discuss these commands in coming chapters. 
NOTE: In a SQL SELECT statement only SELECT and FROM statements are mandatory. Other clauses like WHERE, ORDER BY, GROUP BY, HAVING are optional.

More information    sql full details

SQL Tutorial

SQL (Structured Query Language) is used to modify and access data or information from a storage area called database. This beginner online training sql tutorial website teaches you the basics of SQL code and train you how to write & program SQL queries. I will be sharing my database knowledge on SQL and help you learn programming SQL better. The concepts discussed in this SQL tutorial can be applied to most of database systems. The SQL syntax used to explain the tutorial concepts is similar to the one used in Oracle database.

SQL Intro: What is SQL?

What is SQL? SQL stands for “Structured Query Language” and can be pronounced as “SQL” or “sequel – (Structured English Query Language)”. Defined, SQL is a query language used for accessing and modifying information in the database.

SQL Database Design

IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the relational database management systems (RDBMS). Some of the RDBMS systems are: Oracle, Microsoft SQL server, Sybase etc. Most of these have provided their own implementation thus enhancing it's feature and making it a powerful tool.

SQL Commands: Few SQL Coding Statements?

Few of the sql commands used in sql code programming are: SELECT Statement, UPDATE Statement, INSERT INTO Statement, DELETE Statement, WHERE Clause, ORDER BY Clause, GROUP BY Clause, Subquery Clauses, Joins, Views, GROUP Functions, Indexes etc.

My SQL DataBase

In a simple manner, SQL is a non-procedural, English-like language that processes data in groups of records rather than one record at a time. Few functions of SQL are:

• store data
• modify data
• retrieve data
• modify data
• delete data
• create tables and other database objects
• delete data

  

How to use expressions in SQL SELECT Statement?

Expressions combine many arithmetic operators, they can be used in SELECT, WHERE and ORDER BY Clauses of the SQL SELECT Statement. 
Here we will explain how to use expressions in the SQL SELECT Statement. About using expressions in WHERE and ORDER BY clause, they will be explained in their respective sections. 
The operators are evaluated in a specific order of precedence, when more than one arithmetic operator is used in an expression. The order of evaluation is: parentheses, division, multiplication, addition, and subtraction. The evaluation is performed from the left to the right of the expression. 
For example: If we want to display the first and last name of an employee combined together, the SQL Select Statement would be like 

SELECT first_name + ' ' + last_name FROM employee;




Posted by------Sonugiri0032@twitter.com

WHERE

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

_____________________________________

UPDATE

UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value

UNION ALL

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

UNION

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

TRUNCATE TABLE

TRUNCATE TABLE table_name

SELECT TOP

SELECT TOP number|percent column_name(s)
FROM table_name

SELECT INTO

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name

or

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name

------------------------------------------------------------------------------------------------------------------------------

SELECT *

SELECT *
FROM table_name

SELECT

SELECT column_name(s)
FROM table_name

ORDER BY

SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]

LIKE

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

FULL JOIN

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

RIGHT JOIN

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

LEFT JOIN

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

--------------------------------------------------------------------------------------------------------------------------------

INNER JOIN

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

INSERT INTO

INSERT INTO table_name
VALUES (value1, value2, value3,....)

or

INSERT INTO table_name
(column1, column2, column3,...)
VALUES (value1, value2, value3,....)

IN

SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)

HAVING

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

GROUP BY

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

DROP TABLE

DROP TABLE :

DROP TABLE table_name

DROP INDEX

DROP INDEX

• DROP INDEX table_name.index_name (SQL Server)
• DROP INDEX index_name ON table_name (MS Access)
• DROP INDEX index_name (DB2/Oracle)
• ALTER TABLE table_name
• DROP INDEX index_name (MySQL)

--------------------------------------------------------------------------------------------------------------------------------

DROP DATABASE

DROP DATABASE :

DROP DATABASE database_name

DELETE

DELETE :


DELETE FROM table_name
WHERE some_column=some_value

or

DELETE FROM table_name
(Note: Deletes the entire table!!)

DELETE * FROM table_name
(Note: Deletes the entire table!!)

CREATE VIEW

CREATE VIEW :

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE INDEX

CREATE INDEX :

CREATE INDEX index_name
ON table_name (column_name)

or

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE TABLE

CREATE TABLE :

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)

CREATE DATABASE

CREATE DATABASE:

CREATE DATABASE database_name

BETWEEN

BETWEEN:

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

-------------------------------------------------------------------------------------------------------------------------------

AS (alias)

AS (alias) :


SELECT column_name AS column_alias
FROM table_name

or

SELECT column_name
FROM table_name  AS table_alias

ALTER TABLE

ALTER TABLE : 

ALTER TABLE table_name 
ADD column_name datatype

or

ALTER TABLE table_name
DROP COLUMN column_name

SQL Statement

AND / OR

SELECT column_name(s) FROM table_name WHERE condition AND|OR condition

Parker has recently started learning Windows Store apps development. He has learned creating UI of apps by adding UI elements. Now, he has been assigned a task to develop the UI of the Registration page. The UI of the app will contain the following fields:

Parker has recently started learning Windows Store apps development. He has learned creating
UI of apps by adding UI elements. Now, he has been assigned a task to develop the UI of the
Registration page. The UI of the app will contain the following fields:
User name
First name
Last name
Password
Confirm password
Date of birth
Gender
Receive notifications through email
Accept terms and conditions
In addition, the Register button should be provided on the interface. After adding the UI elements,
the UI should appear, as shown in the following figure.

The Expected UI of the Registration Page
Help Parker to accomplish the preceding task.

Solution:

<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="User Name" VerticalAlignment/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="First Name" VerticalAlignment/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="Last Name"VerticalAlignment/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="Password" VerticalAlignment/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="Confirm Password" VerticalAlignment/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="Date of Birth" VerticalAlignment/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="Gender" VerticalAlignmen/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="Receive notifications through email" VerticalAlignment/>
<TextBlock HorizontalAignment="Left" Margin="96,103,0,0" Text="Accept terms and conditions" VerticalAlignment/>
<PasswordBox Margin="585,303,0,0"HorizontalAlignment="Left"VerticalAlignment/>
<PasswordBox Margin="585,303,0,0"HorizontalAlignment="Left"VerticalAlignment/>
<ComboBox HorizontaiAlignment="Left" Margin="585,353,0,0" VerticalAlignment  />
<ComboBox HorizontaiAlignment="Left" Margin="762,353,0,0" VerticalAlignment/>
<ComboBox HorizontaiAlignment="Left" Margin="939,353,0,0" VerticalAlignment/>
<ToggleSwitch HorizontailAlignment="Left" Margin="585,441,0,0"VerticalAlignment/>
<CheckBox HorizontaiAlignment="Left" margin="585,503,0,0"VerticalAlignment/>                                                                                    
<RadioButton content="Male" HorizontalAlignment="Left" margin="585,403,0,0" verticalAlignment/>
<RadioButton content="Female" HorizontalAlignment="Left" margin="681,553,0,0" verticalAlignment/>

Object-Based Logical Model

The ER model:

Views the real world as a collection of objects or entities and the relationship among them.

An entity:

Is any object, place, person, or activity about which the data is recorded.

Can be categorized as entity type and entity instance.

An entity can be of the following types:

1. Dependent entity
2. Independent entity

A relationship is:

1. Defined as an association among entities.
2. Depicted in a diamond shaped box with the name of the relationship type inside it.  

The following diagram depicts a relationship between the entities (TEACHER and STUDENT).

---------------------------------------------------------------------------------------------------------

• Consider the following statement:

      “A supplier ships certain parts.”

      Identify the entities and the relationship which exists   

      between them.

Entities: SUPPLIER and PARTS
     Relationship: SHIPS

Entities: SUPPLIER and SHIPS
     Relationship: PARTS

Entities: PARTS and SHIPS
     Relationship: SUPPLIER

Entities: SUPPLIER, PARTS, and SHIPS
     Relationship: SUPPLIES

Solution:

Entities: SUPPLIER and PARTS

    Relationship: SHIPS

----------------------------------------------------------------------------------------------------

The three types of relationships are:

1.  One-to-One
2.  One-to-Many (or Many-to-One)
3.  Many-to-Many

An attribute:

• Is a property of a given entity.
• Is depicted as ellipses, labeled with the name of the property. 

----------------------------------------------------------------------------------------------

An attribute is a property of a given entity, which is depicted as ___________, labeled with the name of the property.

Solution:

ellipses

--------------------------------------------------------------------------------------------

Consider the following statement:

     “A supplier ships certain parts. A particular part is not necessarily 
      supplied by only one supplier. No supplier supplies only a single
      part.”

      What type of relationship is possible between the two
      entities?

One-to-One relationship

One-to-Many relationship

Many-to-Many relationship

Many-to-One relationship

Solution:

Many-to-Many relationship

--------------------------------------------------------------------

A subtype:

• Is a subset of another entity.
• Is always dependent on the supertype for its existence.

The attributes of a supertype apply to all of its subtypes.

The following diagram depicts the relationship between supertype (COURSE) and subtypes (STANDLONE and SEMESTER).

---------------------------------------------------------------------------------------------------

There are two types of suppliers. One type of supplier allows credit, while the other type insists on payment in cash before delivery. The manufacturer wishes to maintain separate information on these two types of suppliers. For the credit supplier, “credit period” and “credit limit” have to be recorded. For the cash supplier, “date of payment” has to be stored. Identify the subtype andsupertype entities.

Subtypes: Suppliers and Cash

     Supertype: Credit

Subtype: Credit

     Supertypes: Cash and Suppliers

Subtypes: Cash and Credit

     Supertype: Suppliers

Subtype: Suppliers

     Supertypes: Cash and Credit

Solution:

Subtypes: Cash and Credit

     Supertype: Suppliers

Data Models

A data model is a description of the data in a database.

Data models can be broadly classified into the following categories: 

Object-based logical model

Record-based logical model

------------------------------------------------------------------------------------------------------------------------------

Database Management System

• A database is a collection of logically related data.
• Database Management System (DBMS) is the software required to perform the task of maintaining databases.

Management of data involves:

1. Defining structures for data storage.
2. Providing methods for data manipulation.
3. Providing data security.

• The main advantages of any DBMS is to:

1. Provide an efficient and easy way to store, update, and retrieve data.
2. Manage information about users who interact with the DBMS, and the tasks that they can perform.

--------------------------------------------------------------------------------------------------------------------

Rationale

• Database is an integral part of an organization.

Aspiring database developers should be able to efficiently design and implement databases.

• This knowledge will enable the developers to build robust database solutions.

This course will help students to understand the concepts related to relational databases as well as equip them with basic and advanced knowledge of SQL Server...

-----------------------------------------------------------------------------------------------------------

SQL - RDBMS Concepts

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is table ?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

Remember, a table is the most common and simplest form of data storage in a relational database. Following is the example of a CUSTOMERS table:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

What is field?

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record, or row?

A record, also called a row of data, is each individual entry that exists in a table. For example there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table:

+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

A record is a horizontal entity in a table.

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

For example, a column in the CUSTOMERS table is ADDRESS which represents location description and would consist of the following:

+-----------+
| ADDRESS   |
+-----------+
| Ahmedabad |
| Delhi     |
| Kota      |
| Mumbai    |
| Bhopal    |
| MP        |
| Indore    |
+----+------+

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank which means A field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Contraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

• NOT NULL Constraint: Ensures that a column cannot have NULL value.
• DEFAULT Constraint : Provides a default value for a column when none is specified.
• UNIQUE Constraint: Ensures that all values in a column are different.
• PRIMARY Key: Uniquely identified each rows/records in a database table.
• FOREIGN Key: Uniquely identified a rows/records in any another database table.
• CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
• INDEX: Use to create and retrieve data from the database very quickly.

Data Integrity:

The following categories of the data integrity exist with each RDBMS:

• Entity Integrity : There are no duplicate rows in a table.
• Domain Integrity : Enforces valid entries for a given column by restricting the type, the format, or the range of values.
• Referential integrity : Rows cannot be deleted, which are used by other records.
• User-Defined Integrity : Enforces some specific business rules that do not fall into entity, domain, or referential integrity.

Database Normalization

Database normalization is the process of efficiently organizing data in a database. There are two reasons of the normalization process:

• Eliminating redundant data, for example, storing the same data in more than one tables.
• Ensuring data dependencies make sense.

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure.

Normalization guidelines are divided into normal forms; think of form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure so that it complies with the rules of first normal form, then second normal form, and finally third normal form.

It's your choice to take it further and go to fourth normal form, fifth normal form, and so on, but generally speaking, third normal form is enough.

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)

--------------------------------------------------------------------------------------------------------------------------------

SQL Tutorial

SQL Tutorial

SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language.

This tutorial will give you quick start with SQL.

Audience

This reference has been prepared for the beginners to help them understand the basic to advanced concepts related to SQL languages.

Prerequisites

Before you start doing practice with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, specially RDBMS and what is a computer programming language.

Compile/Execute SQL Programs

If you are willing to compile and execute SQL programs with SQLite DBMS but you do not have a setup for the same, then do not worry. The compileonline.com is available on a high end dedciated server giving you real programming experience with a comfort of single click execution. Yes! it is absolutely free and its online.