basic database concept

8/8/2019 Basic Database Concept

http://slidepdf.com/reader/full/basic-database-concept 1/25

Prepared By

Bhagirathi Muduli

SQL SERVER

DATABASE



DEFINITION

A database is an ordered collection of related data elements intended to meet the

information needs of an organization and designed to be shared by multiple users.

A database is a collection of data elements. Not just a random assembly of data

structures, but a collection of data elements put together deliberately with properorder. The various data elements are linked together in the most logical manner

The data elements in a database are not disjointed structures without any

relationships among them. These are related among themselves and also

pertinent to the particular organization.

The collection of data elements in a database is there for a specific purpose. That

purpose is to satisfy and meet the information needs of the organization.

All authorized users in an organization can share the information stored in its

database. Integrated information is kept in the database for the purpose of

sharing so that all user groups may collaborate and accomplish the organization·s

objectives.

Order Collection

Related Data

Elements

Information Needs

Shared



To Design A Database we need the following things:-

Table/Relation Is a 2 dimensional structure used to hold related information.

Row Is a collection of instance of one thing.

Column/Filed All the information of a single type.

Database Objects

Tables, Views Indexes, Stored Procedures, Triggers etc.



L anguage through which we can interact with the database .

Types of SQL

DDL (Data Definition Language)

DML (Data Manipulation Language)

TCL (Transaction Control Language)

DCL (Data Control Language)

(A) CREATE

(A) SELECT

(A) COMMIT

(A) GRANT

SQL (STRUCTURED QUERY LANGUAGE)

(B) ALTER (C) TRUNCATE (D) DROP

(B) INSERT (C) UPDATE (D) DELETE

(B) ROLLBACK (C) SAVE POINT

(B) REVOKE



SQL DATATYPESEach column value and constant in a SQL statement has a data type, which is associated

with a specific storage format, constraints, and a valid range of values.

1. CHARACTER DATA TYPES 2. DATE & TIME DATA TYPES

3. NUMERIC DATA TYPES 4. INTEGER DATA TYPES

CH AR(N)

VAR CH AR(N)

DATETIME

SMALLDATETIME

DECIMAL (P,S)

NUMERIC

(P,S)

FLOAT(N)

REAL

TINYINT

INT

SAMLLINT

BIGINT



Prevent invalid data entry into the table.CONSTRAINT

Types of Constraints

Not nullUniquePrimary Key Foreign Key

Check Default



CREATE TABLE T ABLE_N AM E( COLU M N_N AM E_1 D AT ATYPE

[ CONSTRAINT CO NST ARI NT_N AM E CO NST RAI NT_TYPE ]

COLU M N_N AM E_2 D AT ATYPE

««««««.COLU M N_N AM E_N D AT ATYPE

)

CREATION OF NEW TABLE

CREATE TABLE E M PL OYEE

( EMP_ID INT(4) CONSTRAINT PK_EMP_ID PRIMARY KEY ,

EMP_NAME VAR CH AR(30) CONSTRAINT NN_EMP_NAME NOT NULL,

EMP_AC TIVE CH AR(1) CONSTRAINT CHK_AC TIVE

CHECK (EMP_AC TIVE IN(¶A·, ¶D·)),

EMAIL VAR CH AR(30) UNIQUE

)

Ex ample



ALTER COMMAND

To add a column:

To change the data type of an existing column:

ALTER TABLE T ABLE_N AM E ADD COLU M N_N AM E D AT ATYPE

ALTER TABLE T ABLE_N AM E DROP COLUMNCO

LU M N_N AM E

ALTER TABLE T ABLE_N AM E ALTER COLUMN

COLU M N_N AM E D AT ATYPE

To delete a column:

To add a constraint for an existing column:

ALTER TABLE T ABLE_N AM E ADD CONSTRAINT

CO NST ARI NT_N AM E CO NST RAI NT_TYPE



EMP_ID EMP_NAME EMP_ACTIVE EMAIL

EMPLOYEE

Add a column called ́ E_CONTACTµ with data type of NUMERIC(12,2)

ALTER TABLE E M PL OYEE ADD E_ CO NT AC T NU M ERI C ( 12,2)

EMP_ID EMP_NAME EMP_ACTIVE EMAIL E_CONTACT

Change the data type of E_CONTACT column with INT(10)

ALTER TABLE E M PL OYEE ALTER COLUMN E_ CO NT AC T I NT ( 10)

Delete the E_CONTACT column

ALTER TABLE E M PL OYEE DROP COLUMN E_ CO NT AC T



EMPLOYEE TABLE STRUCTURE

COLUMN NAME DATATYPE SIZE

EMP_ID INT 4

EMP_NAME VARCHAR 30

EMP_ACTIVE CHAR 1

EMAIL VARCHAR 30



INSERT A RECORD INTO A TABLE

INSERT INTO TABLE T ABLE_N AM E ( COLUMN_LIST) VALUES (VALUE_LIST)

INSERT INTO TABLE T ABLE_N AM E VALUES (VALUE_LIST)

INSERT INTO TABLE E M PL OYEE VALUES

(1001, ¶James Smith·, ¶A·, ¶[email protected]·)

INSERT INTO TABLE E M PL OYEE (EMP_ID, EMP_NAME,EMP_AC TIVE ) VALUES (1002,·Katty Tony·, ¶I·)


1001 James Smith A [email protected]

1002 Katty Tony I



UPDATE T ABLE_N AM E SET

COLU M N_N AM E1=NEW_V ALUE1, COLU M N_N AM E2=NEW_V ALUE2 ,«««««««

COLU M N_N AM EN=NEW_V ALUEN ,[ WHERE CO NDI T I O N ]

UPDATE A RECORD IN A TABLE

Update the EMAIL column filed to ¶[email protected]· of

EMPLOYEE table whose EMP_ID is 1002

UPDATE E M PL OYEE SET E MAI L =¶[email protected] ·

WHERE E M P_ I D=1002



1002 Katty Tony I [email protected]



DELETE A RECORD FROM A TABLE

DELETE FROM T ABLE_N AM E [ WHERE CO NDI T I O N ]



1002 Katty Tony I [email protected]

From the above EMPLOYEE table delete a record whose EMP_ID is 1002

DELETE FROM E M PL OYEE WHERE E M P_ I D=1002





PS_NAMES

EMPLID

FIRST_NAME

MIDDLE_NAME

LAST_NAME

PS_DEPT_TBLDEPT_ID

DESCR

LOCATION

EMAIL_ID

PS_JOBCODE

JOB_CODE

DESCR

PS_JOB

EMPLID

DEPT_ID

JOB_CODE

SALARY



RETRIVE RECORD FROM A TABLE

SELECT F I LED_L I ST FROM T ABLE_N AM E EMPLID FIRST_NAME

1001 James

1002 KattySELECT E M PL I D, F IR ST_N AM E FROM PS_N AM ES

EMPLID FIRST_NAME MIDDLE_NAME LAST_NAME

1001 James Jon Smith

1002 Katty Tony

SELECT * FROM PS_N AM ES



RETRIVE RECORD BY GIVING CONDITION

SELECT F I LED_L I ST FROM T ABLE_N AM E

[ WHERE C L AUSE]

[ GROUP BY C L AUSE]

[ HAVING C L AUSE]

[ ORDER BY C L AUSE]

Retrieve the records from department table where the location is Delhi

SELLECT * FROM PS_DEPT_T BL WHERE UPPER(L OC AT I O N ) =UPPER(¶DELHI· )

Example



GROUP BY CLAUSE

Is used to summarize or group the data.

Calculate the total salary of individual department

Example

SELECT DEPT_ I D, SUM(S AL AR Y ) FROM PS_J OB GROUP BY DEPT_ I D

DEPT_ID SUM(SALARRY)

FIN 49000

SWD 23000

HR 15000

The GROUP BY keyword is used when we are selecting multiple columns from a table (or

tables) and at least one arithmetic operator appears in the SELECT statement. When thathappens, we need to GROUP BY all the other selected columns, i.e. , all columns except the

one(s) operated on by the arithmetic operator.

EMPLID DEPT_ID JOB_CODE SALARY

1001 FIN ASC 25000

1002 SWD ASC 23000

1003 FIN ASC 24000

1004 HR ASC 15000



HAVING CLAUSE

Suppose we need to do the output based on the aggregate function.

The H AVING clause is reserved for aggregate functions.

A SQL statement with the H AVING clause may or may not have include the

GROUP BY clause. EMPLID DEPT_ID JOB_CODE SALARY

1001 FIN ASC 25000

1002 SWD ASC 23000

1003 FIN ASC 24000

1004 HR ASC 15000

Show the departments with

salary below 25000

Example

SELECT DEPT_ I D, SUM(S AL AR Y )

FROM PS_J OB GROUP BY DEPT_ I DHAVING SUM(S AL AR Y )<25000

DEPT_ID SUM(SALARRY)

SWD 23000

HR 15000



ORDER BY COLU M N_N AM E ASC, DESC

ORDER BY CLAUSE

To list the output in a particular order such as in ascending order orin descending order could be based on numerical value or text value.


1001 FIN ASC 25000

1002 SWD ASC 23000

1003 FIN ASC 24000

1004 HR ASC 15000

ExampleShow the records indescending order by salary.

SELECT * FROM PS_J OB ORDER BY S AL AR Y DESC


1001 FIN ASC 25000

1003 FIN ASC 24000

1002 SWD ASC 23000

1004 HR ASC 15000



JOINS JOIN is a query clause that can be used with the SELEC T, UPDATE, and DELETE

data query statements to simultaneously affect rows from multiple tables. Joined tables must each include at least one filed in both tables that contain comparabledata.

Self Join

Inner Join

Outer Join

Left Outer JoinRight Outer JoinFull Outer Join

Cross Join



SELF JOINIn this circumstance, the same table is specified twice with two different aliases in

order to match the data within the same table.

INNER JOIN

Match rows between the two tables specified in the INNER JOIN statement

based on one or more columns having matching data.

OUTER JOIN An OUTER JOIN is a join operation that includes rows that have a match, plus

rows that do not have a match in the other table.

basic database concept

Documents