Archive

Archive for May, 2008

sql sneak peek

May 25, 2008 1 comment

I have developed this article to assist my sister in understanding RDBMS. The database I have used is “PostgreSQL”! However if any of the readers are trying out the SQL queries listed below in any other RDBMS (like MS SQL Server, Oracle, MySQL) the SQL statements may be altered accordingly to run the queries against the database being used.

Scenario: You have to design the database for “Employee Management System” which includes tables, SQL queries and related stored procedures.

Description: As the name indicates “Employee Management System” captures every information pertaining to each employee of a particular organization.

Step 1: Identify the data. Example: “Balaji” is the employee name with employee id “1234” working with “xyz” organization, as a “Software Developer” for “abc” department, from “2003” to “till date”, earning “$1000” per month..

Step 2: Identify the tables that need to be created. Example: From the above example in step (1), you have the raw data; hence the possible tables that are needed are as follows: “Employee Information”, “Organization Information”, “Department Information”, “Role Information”, “Location Information” and “Account Information”.

Lets have a theoretical glance on some database concepts before we jump on into table design.

SQL Statements Categories:

DDL – Data Definition Language:statements used to define the database structure or schema.

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – deletes objects from the database
  • TRUNCATE – removes all records from the table and frees memory allocated for it.
  • COMMENT – adds comment to the data dictionary
  • RENAME – rename an object

DML – Data Manipulation Language:statements used for managing data within schema objects.

  • SELECT – retrieve data from the database
  • INSERT – insert data into a table
  • UPDATE – updates existing data within a table
  • DELETE – deletes all records from a table, the memory space remains as such
  • MERGE – UPSERT operation (insert/update)
  • CALL – call a PL/SQL or Java subprogram
  • EXPLAIN PLAN – explain access path to data
  • LOCK TABLE – control concurrency

DCL – Data Control Language:

  • GRANT – gives user’s access priviledge to database
  • REVOKE – withdraws access priviledges given with the GRANT command

TCL – Transaction Control:statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

  • COMMIT – save work done
  • SAVEPOINT – identify a point in a transaction to which you can later rollback
  • ROLLBACK – restore database to original since the last COMMIT
  • SET TRANSACTION – change transaction option like isolation level and what rollback segment to use

Constraints: The synonym for a constraint would generally mean restriction or limitation. Henceforth applying certain restriction (or) limitation on columns and tables is called a constraint. Applying a constraint would generally give you a greater control over the data in a table. The following are different types of constraints:

Check Constraint: This type of constraint allows you to specify a value for a certain column wherein it satisfies an arbitrary expression. For example let’s create a table (item) which stores item name and item price and item tax; Assume that the item tax should be 9% of item price. Here the possible checks are: ‘item price’ column should have values greater than ‘0’ and ‘item tax’ column should have values greater than ‘0’ and lesser than (item price / 10).

CREATE TABLE Items 
(
ItemId INT PRIMARY KEY,
ItemPrice INT CHECK (ItemPrice > 0),
ItemTax INT CHECK (ItemTax > 0), CHECK (ItemTax < (ItemPrice / 10))
);

Not-Null Constraint: The not-null constraint doesnot allow you to specify null values. It is always written as a column constraint. The not-null constrint is equivalent to creating a check constraint CHECK (column_name IS NOT NULL); The inverse of a NOT NULL constraint is a NULL constraint, this is used when you want a column to allow null values. From the example below: the table ‘Cashiers’ contains the usage of ‘NOT NULL’ and ‘NULL’ constraints.

CREATE TABLE Cashiers 
(
CashierId INT PRIMARY KEY,
CashierName VARCHAR(20) NOT NULL,
CashierLocation VARCHAR(20) CHECK (CashierLocation IS NOT NULL)
Comments VARCHAR(100) NULL
);

Unique Constraint: Unique constraint is used when you need to ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table. An example for its usage is as shown below:

CREATE TABLE Stores 
(
StoreId INT PRIMARY KEY,
StoreName VARCHAR(50) UNIQUE,
StoreCity VARCHAR(30),
UNIQUE(StoreCity),
StoreState VARCHAR(30),
StoreCountry VARCHAR(30),
UNIQUE(StoreState,StoreCountry),
StoreZipCode INT CONSTRAINT Zip_Code_Must_Be_Unique UNIQUE
);

when written as a column constraint:

StoreName VARCHAR(50) UNIQUE,

when written as a table constraint:

StoreCity VARCHAR(30),
UNIQUE(StoreCity),

when unique constraint refers to a group of columns:

StoreState VARCHAR(30),
StoreCountry VARCHAR(30),
UNIQUE(StoreState,StoreCountry),

When you need to assign a name for unique constraint:

StoreZipCode INT CONSTRAINT Zip_Code_Must_Be_Unique UNIQUE

Primary Keys: This constraint is used when you a column is used as a unique identifier for rows in tables. A table can have only one primary key. Technically primary key is combination of unique constraint and a not null constraint.

CREATE TABLE TransactionLog 
(
LogId INT PRIMARY KEY,
LogName VARCHAR(20) NULL
);

It can be also written as;

CREATE TABLE TransactionLog 
(
LogId INT UNIQUE NOT NULL,
LogName VARCHAR(20) NULL
);

Foreign Keys: This constraint is used when you want the values of a column to match with values appearing in some row of another table. This way we can maintain the referential integrity between two related tables A table can contain one or more foreign key constraints. This is used to maintain many-to-many relationships between tables. An example below depicts various ways of defining a foreign key constraint.

CREATE TABLE Transactions
(
TransactionId INT PRIMARY KEY,
TransactionDate DATE DEFAULT (CURRENT_DATE),
ItemId INT REFERENCES Items (ItemId),
CashierId INT REFERENCES Cashiers,
StoreId INT UNIQUE,
FOREIGN KEY (StoreId) REFERENCES Stores(StoreId)
);

– TIP 1: Date column keyword.
– PostgreSQL: DATE
– MSSQLServer: DATETIME

–TIP 2: Function to get current date.
–PostgreSQL: CURRENT_DATE
–MSSQLServer: GETDATE()

–TIP 3: When the number and type of the constrained columns does not match the number and type of the referenced columns.
–PostgreSQL: ERROR: number of referencing and referenced columns for foreign key disagree
–MSSQLServer:Number of referencing columns in foreign key differs from number of referenced columns, table ‘Transactions’.

Now, lets come back to our main course…

Step 3: Create tables along with the relations and necessary constraints. Convention: “Employee Information” table can hold table name as “EmpTable”.

SQL Queries for creating Tables:

CREATE DATABASE ems;

CREATE SCHEMA demo;

– Table: demo.”LocTable”
CREATE TABLE demo.“LocTable”
(
“LocId” integer NOT NULL,
“LocName” character(100) NOT NULL,
“LocComments” character(200),
CONSTRAINT demo_loc_primary_key PRIMARY KEY (“LocId”)
);

– Table: demo.”OrgTable”
CREATE TABLE demo.“OrgTable”
(
“OrgId” integer NOT NULL,
“OrgName” character(100) NOT NULL,
“OrgComments” character(200),
CONSTRAINT “demo_org_primary_Key” PRIMARY KEY (“OrgId”)
);

– Table: demo.”RoleTable”
CREATE TABLE demo.“RoleTable”
(
“RoleId” integer NOT NULL,
“RoleName” character(100) NOT NULL,
“RoleComments” character(200),
CONSTRAINT demo_role_primary_key PRIMARY KEY (“RoleId”)
);

– Table: demo.”AccTable”
CREATE TABLE demo.“AccTable”
(
“AccId” integer NOT NULL,
“AccSalary” double precision NOT NULL,
“AccStartDate” date NOT NULL,
“AccEndDate” date,
“AccComments” character(200),
CONSTRAINT demo_acc_primary_key PRIMARY KEY (“AccId”)
);

– Table: demo.”DeptTable”
CREATE TABLE demo.“DeptTable”
(
“DeptId” integer NOT NULL,
“DeptName” character(100) NOT NULL,
“DeptLocationId” integer NOT NULL,
“DeptComments” character(200),
CONSTRAINT demo_dept_primary_key PRIMARY KEY (“DeptId”),
CONSTRAINT demo_dept_loc_foreign_key FOREIGN KEY (“DeptLocationId”)
REFERENCES demo.“LocTable” (“LocId”)
ON UPDATE NO ACTION ON DELETE NO ACTION
);

– Table: demo.”EmpTable”
CREATE TABLE demo.“EmpTable”
(
“EmpId” integer NOT NULL,
“EmpFirstName” character(50) NOT NULL,
“EmpMiddleName” character(50),
“EmpLastName” character(50) NOT NULL,
“OrgId” integer NOT NULL,
“DeptId” integer NOT NULL,
“AccId” integer NOT NULL,
“RoleId” integer NOT NULL,
“EmpComments” character(200),
“EmpLocationId” integer NOT NULL,
CONSTRAINT demo_emp_primary_key PRIMARY KEY (“EmpId”),
CONSTRAINT demo_emp_acc_foreign_key FOREIGN KEY (“AccId”)
REFERENCES demo.“AccTable” (“AccId”)
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT demo_emp_dept_foreign_key FOREIGN KEY (“DeptId”)
REFERENCES demo.“DeptTable” (“DeptId”)
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT demo_emp_org_foreign_key FOREIGN KEY (“OrgId”)
REFERENCES demo.“OrgTable” (“OrgId”)
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT demo_emp_role_foreign_key FOREIGN KEY (“RoleId”)
REFERENCES demo.“RoleTable” (“RoleId”)
ON UPDATE NO ACTION ON DELETE NO ACTION
);

– Table: demo.”EmpAuditTable”
CREATE TABLE demo.“EmpAuditTable”
(
“AuditId” integer PRIMARY KEY,
“AuditDate” DATETIME NOT NULL,
“AuditSummary” character(50) NULL
);

SQL Queries for inserting data into Tables:

– Table: demo.”LocTable”
INSERT INTO demo.“LocTable”(“LocId”, “LocName”, “LocComments”)
VALUES (001, ‘Bangalore’, ‘Branch Office’);

INSERT INTO demo.“LocTable”(“LocId”, “LocName”, “LocComments”)
VALUES (002, ‘Chennai’, ‘Corporate Office’);

INSERT INTO demo.“LocTable”(“LocId”, “LocName”, “LocComments”)
VALUES (003, ‘San Francisco’, ‘Head Quarters’);

– Table: demo.”OrgTable”
INSERT INTO demo.“OrgTable”(“OrgId”, “OrgName”, “OrgComments”)
VALUES (001, ‘Computer Sciences Corporation’, ‘Consulting’);

INSERT INTO demo.“OrgTable”(“OrgId”, “OrgName”, “OrgComments”)
VALUES (002, ‘Wipro Technologies’, ‘System Integration & Design’);

INSERT INTO demo.“OrgTable”(“OrgId”, “OrgName”, “OrgComments”)
VALUES (003, ‘Infosys Technologies’, ‘Business Process Outsourcing’);

– Table: demo.”RoleTable”
INSERT INTO demo.“RoleTable”(“RoleId”, “RoleName”, “RoleComments”)
VALUES (001, ‘Project Trainee’, ‘Document Specialist & Application Tester’);

INSERT INTO demo.“RoleTable”(“RoleId”, “RoleName”, “RoleComments”)
VALUES (002, ‘Software Engineer’, ‘Application Software Developer’);

INSERT INTO demo.“RoleTable”(“RoleId”, “RoleName”, “RoleComments”)
VALUES (003, ‘Data Architect’, ‘Data Modelling and Database Design’);

– Table: demo.”AccTable”
– TIP: When using SQL Server the Date should be in ‘MM/DD/YYYY’ format
– TIP: When using PostgreSQL the Date should be in ‘YYYY/MM/DD’ format
INSERT INTO demo.“AccTable”(“AccId”, “AccSalary”, “AccStartDate”, “AccEndDate”, “AccComments”)
VALUES (001, 100000, ‘2000-01-01′, ‘2007-12-31′, ‘Employee Resigned’);

INSERT INTO demo.“AccTable”(“AccId”, “AccSalary”, “AccStartDate”, “AccEndDate”, “AccComments”)
VALUES (002, 200000, ‘2007-05-04′, null, ‘Offshore Employee’);

INSERT INTO demo.“AccTable”(“AccId”, “AccSalary”, “AccStartDate”, “AccEndDate”, “AccComments”)
VALUES (003, 300000, ‘2005-10-03′, null, ‘Onsite Employee’);

– Table: demo.”DeptTable”
INSERT INTO demo.“DeptTable”(“DeptId”, “DeptName”, “DeptLocationId”, “DeptComments”)
VALUES (001, ‘Accounts Department’, 001, ‘Account and Payroll Management’);

INSERT INTO demo.“DeptTable”(“DeptId”, “DeptName”, “DeptLocationId”, “DeptComments”)
VALUES (002, ‘Human Resources Department’, 002, ‘Employee and Corporate Management’);

INSERT INTO demo.“DeptTable”(“DeptId”, “DeptName”, “DeptLocationId”, “DeptComments”)
VALUES (003, ‘IT Department’, 003, ‘Systems and Infrastructure Management’);

– Table: demo.”EmpTable”
INSERT INTO demo.“EmpTable”(“EmpId”, “EmpFirstName”, “EmpMiddleName”, “EmpLastName”, “OrgId”,
“DeptId”, “AccId”, “RoleId”, “EmpComments”, “EmpLocationId”)
VALUES (001, ‘Balaji’, ‘Baskar’, ‘Mudhaliar’, 001, 002, 003, 001, ‘Hired in Campus Interview’, 003);

INSERT INTO demo.“EmpTable”(“EmpId”, “EmpFirstName”, “EmpMiddleName”, “EmpLastName”, “OrgId”,
“DeptId”, “AccId”, “RoleId”, “EmpComments”, “EmpLocationId”)
VALUES (002, ‘Kalappa’, ”, ‘Pattar’, 002, 003, 001, 003, ‘Hired in Job Fair’, 001);

INSERT INTO demo.“EmpTable”(“EmpId”, “EmpFirstName”, “EmpMiddleName”, “EmpLastName”, “OrgId”,
“DeptId”, “AccId”, “RoleId”, “EmpComments”, “EmpLocationId”)
VALUES (003, ‘Vasanth’, ‘Kumar’, ‘Gangappa’, 003, 001, 002, 002, ‘Applied thro Naukri’, 002);

SQL Queries for selecting data from Tables:

SELECT “LocId”, “LocName”, “LocComments”
FROM demo.“LocTable”;

SELECT “OrgId”, “OrgName”, “OrgComments”
FROM demo.“OrgTable”;

SELECT “RoleId”, “RoleName”, “RoleComments”
FROM demo.“RoleTable”;

SELECT “AccId”, “AccSalary”, “AccStartDate”, “AccEndDate”, “AccComments”
FROM demo.“AccTable”;

SELECT “DeptId”, “DeptName”, “DeptLocationId”, “DeptComments”
FROM demo.“DeptTable”;

SELECT “EmpId”, “EmpFirstName”, “EmpMiddleName”, “EmpLastName”, “OrgId”,
“DeptId”, “AccId”, “RoleId”, “EmpComments”, “EmpLocationId” FROM demo.“EmpTable”;
Advertisements
%d bloggers like this: