DATABASE MANAGMENT SYSTEM SQL QUERIES.pptx

Database System
LAB 1
Arifa Shafi

Objectives
• After completing this lesson, you should be able to:
• Understand the basics of databases and their importance
• List the features of PostgreSQL
• Discuss the relational database model
• Use pgAdmin to create a database, tables, and run basic
SQL queries

What is a Database?
• A database is an organized collection of data, typically stored
and accessed electronically from a computer system. Databases
are managed by a Database Management System (DBMS),
which allows users to store, retrieve, and modify data
efficiently.
• Why Use Databases?
• Organize and store large amounts of information
• Enable efficient data retrieval and manipulation
• Ensure data integrity and consistency
• Support multiple users and applications

Introduction to PostgreSQL
• PostgreSQL is a powerful, open-source relational database
management system (RDBMS) known for its:
• Scalability: Handles small to large-scale applications
• Reliability: Robust and secure architecture
• Flexibility: Supports SQL, JSON, XML, and advanced data
types
• Community Support: Open-source with a strong
community
• Cross-Platform: Runs on Windows, macOS, Linux, and more
• PostgreSQL is widely used for web applications, data
analytics, and enterprise solutions.

Introduction to pgAdmin
• pgAdmin is a free, open-source graphical tool for managing
PostgreSQL databases. It provides:
• A user-friendly interface to interact with PostgreSQL
• Tools to create databases, tables, and execute SQL queries
• Visual query builder and schema management
• Monitoring and administration features
• In this lab, you will use pgAdmin to perform basic database
operations.

Relational Database Concepts
• A relational database organizes data into tables, where each table
represents an entity (e.g., Employees, Departments). Tables are related
through keys.
• Key Terminology
• Table: A collection of rows and columns storing data about a specific entity.
• Row: A single record in a table (e.g., one employee’s data).
• Column: An attribute of an entity (e.g., employee name, ID).
• Primary Key (PK): A unique identifier for each row in a table.
• Foreign Key (FK): A column that links one table to another, referencing a
primary key.
• SQL: Structured Query Language, used to interact with the database.

Sql Basic Queries
• SELECT - Retrieve data from a table
• CREATE - Create database or table
• INSERT - Insert new records
• UPDATE - Update existing records
• DELETE - Delete records
• DROP - Delete table or database
• ALTER - Modify table structure
• TRUNCATE - Remove all data but keep structure
• WHERE - Filter rows
• ORDER BY - Sort results
• GROUP BY - Group rows
• HAVING - Filter grouped results
• JOIN - Combine multiple tables
• DISTINCT - Remove duplicates
• LIMIT - Restrict number of results

1. SELECT Statement
• SELECT is used to retrieve data from one or more tables.
• Syntax: SELECT column1, column2 FROM table_name WHERE condition;
• Example: SELECT name, age FROM students WHERE age > 18;

2. CREATE
• Used to create a new database or table.
• Example: CREATE DATABASE school;
• Example: CREATE TABLE students (id INT, name VARCHAR(50));

3. INSERT
• Inserts new rows into a table.
• Example: INSERT INTO students (id, name, age) VALUES (1, 'Ali', 20);

4. UPDATE
• Modifies existing records in a table.
• Example: UPDATE students SET id=2;

5. DELETE
• Deletes rows from a table.
• Example: DELETE FROM students WHERE id= 1;

6. DROP
• Removes a table or database permanently.
• Example: DROP TABLE students;
• Example: DROP DATABASE school;

7. ALTER
• Used to modify table structure.
• Example: ALTER TABLE students ADD email VARCHAR(100);

8. TRUNCATE
• Deletes all data from a table but keeps structure.
• Example: TRUNCATE TABLE students;

9. WHERE
• Filters records based on a condition.
• Example: SELECT * FROM students WHERE grade = 'A';

10. ORDER BY
• Sorts retrieved data in ascending or descending order.
• Example: SELECT * FROM students ORDER BY age DESC;

11. GROUP BY & HAVING
• GROUP BY groups rows based on column values.
• HAVING filters grouped results.
• Example: SELECT grade, COUNT(*) FROM students GROUP BY grade HAVING COUNT(*) > 2;

12. JOIN
• Combines data from multiple tables.
• Example: SELECT students.name, courses.course_name FROM students JOIN courses ON
students.id = courses.student_id;

13. DISTINCT & LIMIT
• DISTINCT removes duplicate values.
• Example: SELECT DISTINCT grade FROM students;
• LIMIT restricts number of results.
• Example: SELECT * FROM students LIMIT 5;

Example
• Example
• Table: EMPLOYEES
• Columns: employee_id (PK), name, department_id (FK)
• Table: DEPARTMENTS
• Columns: department_id (PK), department_name

Lab: Getting Started with pgAdmin
• In this lab, you will:
• Connect to a PostgreSQL server using pgAdmin
• Create a database
• Create tables (EMPLOYEES and DEPARTMENTS)
• Insert sample data
• Run basic SQL queries

Lab Instructions
• Step 1: Connect to PostgreSQL in pgAdmin
• Open pgAdmin.
• In the left panel, expand Servers and click on PostgreSQL (or
your server name).
• If prompted, enter your PostgreSQL username and password
(default: postgres).
• Step 2: Create a Database
• Right-click Databases in the left panel, select Create > Database.
• Name the database company_db and click Save.

• SELECT name, age
• FROM students
• WHERE age > 18;
SELECT
Statement

Step 3: Create Tables
• CREATE TABLE departments (
• department_id INTEGER PRIMARY KEY,
• department_name VARCHAR(50) NOT NULL,
• location VARCHAR(50)
• );
• CREATE TABLE employees (
• employee_id INTEGER PRIMARY KEY,
• name VARCHAR(50) NOT NULL,
• job_title VARCHAR(50),
• department_id INTEGER,
• FOREIGN KEY (department_id) REFERENCES departments(department_id)
• );

Step 4: Insert Sample Data
• INSERT INTO departments (department_id, department_name, location)
• VALUES
• (1, 'HR', 'New York'),
• (2, 'IT', 'San Francisco');
• INSERT INTO employees (employee_id, name, job_title, department_id)
• VALUES
• (101, 'John Doe', 'HR Manager', 1),
• (102, 'Jane Smith', 'Developer', 2),
• (103, 'Alice Johnson', NULL, 1);

Step 5: Run Basic Queries
• Select all employees:
• SELECT * FROM employees;
• Select employees in a specific department:
• SELECT e.name, d.department_name
• FROM employees e
• JOIN departments d ON e.department_id = d.department_id;

Count employees per department:
• SELECT d.department_name, COUNT(e.employee_id) AS
employee_count
• FROM departments d
• LEFT JOIN employees e ON d.department_id =
e.department_id
• GROUP BY d.department_name;

Step 6: Explore pgAdmin
• View the created tables under company_db > Schemas >
public > Tables.
• Use the View Data option (right-click a table) to see the
data.
• Experiment with the Query Tool to write your own SQL
queries.

SQL Basics
• SQL (Structured Query Language) is used to communicate with the
database. Common SQL statements include:
• Data Retrieval:
• SELECT: Retrieves data from tables
• Data Manipulation Language (DML):
• INSERT: Adds new rows
• UPDATE: Modifies existing rows
• DELETE: Removes rows
• Data Definition Language (DDL):
• CREATE: Creates database objects (e.g., tables)
• ALTER: Modifies database objects
• DROP: Deletes database objects

Lab Assignment
• Create a new table called jobs with columns: job_id (PK),
job_title, and min_salary.
• Insert at least three sample rows into the jobs table.
• Write a query to list all employees and their job titles from
the employees and jobs tables (assume job_title in
employees is replaced with job_id as a foreign key).
• Submit your SQL queries and a screenshot of the query
results in pgAdmin.

DATABASE MANAGMENT SYSTEM SQL QUERIES.pptx

More Related Content

Similar to DATABASE MANAGMENT SYSTEM SQL QUERIES.pptx

Recently uploaded

DATABASE MANAGMENT SYSTEM SQL QUERIES.pptx