Introduction to SQL

SQL Training
Structured Query Language
August 2018

1
Agenda

 What is SQL
 Why use SQL
 SQL Developer
 Tables, Fields, Records and Columns
 Data Types
 NULL value
 SQL Constraints
 SQL Commands
 The Database
 SQL Indexes
 PeopleSoft HRMS Tables
 Entity Relationship Models
 Syntax in SQL
 Exporting Data
 Demo

2
What is SQL

 SQL is Structured Query Language, which is a computer language for storing,

manipulating and retrieving data stored in a relational database.

 SQL is the standard language for Relational Database System. All the Relational
Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase,
Informix, Postgres and SQL Server use SQL as their standard database language.

 This tutorial will give you a quick start to SQL. It covers most of the topics required
for a basic understanding of SQL and to get a feel of how it works.

3
Why use SQL

 SQL is widely popular because it offers the

following advantages:
– Allows users to access data in the relational
database management systems.
– Allows users to describe the data.
– Allows users to define the data in a database and
manipulate that data.
– Allows to be embed within other languages using
SQL modules, libraries & pre-compilers.
– Allows users to create and drop databases and
tables.
– Allows users to create view, stored procedure,
functions in a database.
– Allows users to set permissions on tables,
procedures and views.

4
SQL Developer

 Oracle SQL Developer is an

Integrated development
environment for working with
SQL in Oracle databases.

 A request would have to be

submitted to have SQL
Developer installed on the
users desktop.

 A request would also need to

be submitted to request
access to the database

5
 SQL Developer

 After launching Application, click the

plus sign to setup an new connection

 Enter Connection Name

 Enter Username
 Enter Password
 Enter Hostname
 Enter Port
 Enter Service name
 Click Save Password
 Click Test
 Click Save

6
SQL Developer

 Click File New to

begin a SQL session
 Select SQL File
 Click OK
 Give the File a Name
 Click OK

7
SQL Developer

 Click the dropdown on the right hand side to choose database

8
SQL Developer

 Write SQL and click green error to run SQL

 Script Output will display results

9
 Tables, Fields, Records and Columns

What is a table?
 The data is stored in database objects which are called as tables. This table is
basically a collection of related data entries and it consists of numerous columns
and rows.

What is a 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.

What is a Record or a Row?

 A record is also called as a row of data. Each row is an individual entry that exists in
a table.

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

10
Data Types

 SQL Data Type is an attribute that specifies the type of data of any object. Each
column, variable and expression has a related data type in SQL. You can use these
data types while creating your tables. You can choose a data type for a table
column based on your requirement. The most common datatypes are:

11
What is a 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 the one that has been left
blank during a record creation.

12
SQL Constraints

 Constraints are the rules enforced on data columns on a 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.
 Constraints can either be column level or table level. Column level constraints are
applied only to one column whereas, table level constraints are applied to the entire
table.
 Following are some of the most commonly used constraints available in SQL:
– NOT NULL Constraint: Ensures that a column cannot have a NULL value.
– DEFAULT Constraint: Provides a default value for a column when none is specified.
– UNIQUE Constraint: Ensures that all the values in a column are different.
– PRIMARY Key: Uniquely identifies each row/record in a database table.
– FOREIGN Key: Uniquely identifies a row/record in any other database table.
– CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy
certain conditions.
– INDEX: Used to create and retrieve data from the database very quickly.

13
SQL Commands

 SQL Commands fit into two broad categories

– Data definition language (DDL)

• SQL includes commands to:
o Create database objects, such as tables, indexes, and views
o Define access rights to those database objects

– Data manipulation language(DML)

• Includes commands to insert, update, delete, and retrieve data within database
tables

14
 SQL Commands - DDL

 DDL - Data Definition Language

15
 SQL Commands - DML

 DDL - Data Manipulation Language

16
SQL Commands - DML

17
 SQL Commands - DCL

 DCL - Data Control Language

18
 The Database

 RDBMS creates physical files that will hold database

 Authentication
– Process through which DBMS verifies that only registered users
are able to access database
– Log on to RDBMS using user ID and password created by
database administrator
 Schema
– Group of database objects—such as tables and indexes—that are
related to each other

19
SQL Indexes
 When primary key is declared, DBMS automatically creates unique index

 Often need additional indexes

 Using CREATE INDEX command, SQL indexes can be created on basis of any
selected attribute

 Composite index
– Index based on two or more attributes
– Often used to prevent data duplication

20
PeopleSoft HRMS Tables/Records
 PeopleTools Tables – PeopleTools tables contain meta-data about the PeopleSoft applicaton.
– PSMENUDEFN – Menu Names
– PSPNLDEFN – Page Names

 PeopleSoft Control Tables - Control tables store information that is used to process and validate the
day-to-day business activities (transactions) users perform with PeopleSoft HCM applications. The
information stored in control tables is common and shared across an organization, for example,
master lists of customers, vendors, applications, items, or charts of accounts. By storing this shared
information in a central location, control tables help to reduce data redundancy, maintain data
integrity, and ensure that users have access to the same basic information. The information stored in
control tables is generally static and is updated only when fundamental changes occur to business
policies, organizational structures, or processing rules.
– COMPANY_TBL
– LOCATION_TBL

 PeopleSoft Transaction Tables - Transaction tables store information about the day-to-day business
activities (transactions) users perform with PeopleSoft HCM applications. The information stored in
transaction tables often changes and is updated more frequently than the information stored in control
tables
– JOB

 PeopleSoft Prompt Tables - Prompt tables are tables that are associated with fields on PeopleSoft
application pages and which display valid data values for those fields when a user selects a prompt or
search option. The data values stored in prompt tables are retrieved from control tables, transaction
tables, or other PeopleSoft tables

21
Looking Up PeopleSoft Records
 Look up Records using Entity relations diagrams (ERDs). This module is available in DEMO.
 Entity relations diagrams (ERDs) capture the relationships between, details of, and constraints
imposed on the data defined in these models. ERDs provide a visual representation of the
database.

• Navigate to : Data Models

•  Query  Query Manager​
• ​
• Navigate to a page that you
• want to see the Diagram and
Records
• A PDF will be create

22
Looking Up PeopleSoft Records - Continued
 In this example we used the Department Component

Department ERD

23
Using SQL to find Records and Fields
 Many a times you come across a situation when you need to find records behind a page or records
associated with a field and so on. Most of such situations arise from the need to debug an issue and
find a resolution for the same. Here are a few helpful SQL queries, which will come handy in such
situations.
 1. SQL Query to find all the Records and Fields used in a PeopleSoft page:
– SELECT recname, fieldname
– FROM   pspnlfield
– WHERE  pnlname = PageName;
– * Replace PageName with the actual Page name you want to reference

 2. SQL Query to find all the Records where a particular PeopleSoft field is used:
– SELECT DISTINCT recname, fieldname
FROM   psrecfield
WHERE  fieldname = FieldName;
– * Replace PageName with the actual Page name you want to reference

 3. SQL query to find all the page names where a field is used from a particular record:
– SELECT pnlname
– FROM   pspnlfield
– WHERE  recname =RecordName
– AND fieldname = FieldName;
– * Replace RecordName and FieldName with the actual record name and field name respectively. You can also remove the and
condition if needed.

24
Common Syntax in SQL

 SQL SELECT Statement

– SELECT column1, column2....columnN
– FROM table_name;

 SQL DISTINCT Clause

– SELECT DISTINCT column1, column2....columnN
– FROM table_name;

 SQL WHERE Clause

– SELECT column1, column2....columnN
– FROM table_name
– WHERE CONDITION;

 SQL AND/OR Clause

– SELECT column1, column2....columnN
– FROM table_name
– WHERE CONDITION-1 {AND|OR} CONDITION-2;

25
Common Syntax in SQL continued

 SQL IN Clause
– SELECT column1, column2....columnN
– FROM table_name
– WHERE column_name IN (val-1, val-2,...val-N);

 SQL BETWEEN Clause

– SELECT column1, column2....columnN
– FROM table_name
– WHERE column_name BETWEEN val-1 AND val-2;

 SQL LIKE Clause

– SELECT column1, column2....columnN
– FROM table_name
– WHERE column_name LIKE { PATTERN };

 SQL ORDER BY Clause

– SELECT column1, column2....columnN
– FROM table_name
– WHERE CONDITION
– ORDER BY column_name {ASC|DESC};

26
Common Syntax in SQL continued

 SQL GROUP BY Clause

– SELECT SUM(column_name)
– FROM table_name
– WHERE CONDITION
– GROUP BY column_name;

 SQL COUNT Clause

– SELECT COUNT(column_name)
– FROM table_name
– WHERE CONDITION;

 SQL HAVING Clause

– SELECT SUM(column_name)
– FROM table_name
– WHERE CONDITION
– GROUP BY column_name
– HAVING (arithematic function condition);

27
Other Syntax in SQL primarily used by power user

 SQL CREATE TABLE Statement

 SQL DROP TABLE Statement
 SQL CREATE INDEX Statement
 SQL DROP INDEX Statement
 SQL DESC Statement
 SQL TRUNCATE TABLE Statement
 SQL ALTER TABLE Statement
 SQL ALTER TABLE Statement (Rename)
 SQL INSERT INTO Statement
 SQL UPDATE Statement
 SQL DELETE Statement
 SQL CREATE DATABASE Statement
 SQL DROP DATABASE Statement
 SQL USE Statement
 SQL COMMIT Statement
 SQL ROLLBACK Statement

28
Exporting Data
 Data can be exported from SQL Developer
– Run a Query
– Right click in the result area
– Click Export
–

29
Exporting Data
– Click Format Dropdown
– Select XLS for excel

30
Exporting Data
– Click Browse to select where to save the file
– Name the export File
– Click Save

31
Exporting Data
– Click Next

32
Exporting Data
– Click Finish

33
Exporting Data
– Data will be exported to an excel spreadsheet

34
Demo

 SQL SELECT Statement

 SQL DISTINCT Clause
 SQL WHERE Clause
 SQL AND/OR Clause
 SQL IN Clause
 SQL BETWEEN Clause
 SQL LIKE Clause
 SQL ORDER BY Clause
 SQL GROUP BY Clause
 SQL COUNT Clause
 SQL HAVING Clause
 Exporting Data to Excel

35
Questions

36