assignment 1 (Introd to SQL)

divya sachan

@divyasachan

Created

Oct 31, 2025

Last Modified

4 months ago

Assignment 1

Objective:

Create tables and insert data into tables using SQL queries.

⭐ Introduction to SQL

SQL (Structured Query Language) is a non-procedural language — you specify what you want, not how to get it. SQL uses English-like keywords and has the following components:

1️⃣ DDL (Data Definition Language)

Used to define and modify database structure.
Examples: CREATE, ALTER, DROP

2️⃣ DML (Data Manipulation Language)

Used to insert, update, delete, and retrieve data.
Examples: INSERT, UPDATE, DELETE, SELECT

3️⃣ Integrity

SQL allows defining integrity constraints to ensure data correctness.

4️⃣ Authorization

We can define access rights for users on tables and views.

🧱 Data Definition Language (DDL)

DDL allows you to define:

Schema (structure) of tables
Datatypes for each attribute
Integrity constraints
Indexing
Authorization control
Physical storage structure

📌 Domain Types in SQL

Datatype	Description
`CHAR(n)`	Fixed-length text string
`VARCHAR2(n)`	Variable-length string (max size = n)
`NUMBER(p, d)`	Numeric value with precision p and scale d
`DATE`	Stores year, month & day

🛠️ Creating a Table – Syntax

sql

CREATE TABLE table_name (
    column_name datatype(size),
    column_name datatype(size)
);

Create a table from another table

sql

CREATE TABLE new_table
AS SELECT column1, column2 FROM existing_table;

✨ Insert Data – Syntax

sql

INSERT INTO table_name (col1, col2, ...)
VALUES (value1, value2, ...);

Insert from another table

sql

INSERT INTO table_name
SELECT col1, col2 FROM another_table;

Insert selective data

sql

INSERT INTO table_name
SELECT col1, col2 FROM another_table
WHERE condition;

🔍 Retrieving Data – SELECT

sql

SELECT * FROM table_name;               -- All columns
SELECT col1, col2 FROM table_name;      -- Specific columns
SELECT DISTINCT col1 FROM table_name;   -- Remove duplicates

With condition

sql

SELECT col1, col2 FROM table_name
WHERE condition;

🧪 Assignment – Lab 1

Q1: Create Table `client_master`

Column Name	Datatype	Size
client_no	VARCHAR2	6
name	VARCHAR2	20
city	VARCHAR2	15
state	VARCHAR2	15
pincode	NUMBER	6
bal_due	NUMBER	(10,2)

Q2: Insert Data

Client_no	Name	City	Pincode	State	Bal_due
0001	Ivan	Bombay	400054	Maharashtra	15000
0002	Vandana	Madras	780001	Tamilnadu	0
0003	Pramada	Bombay	400057	Maharashtra	5000
0004	Basu	Bombay	null	Maharashtra	0
0005	Ravi	Delhi	100001	null	2000
0006	Rukmini	Bombay	400050	Maharashtra	0

🗃️ Q1: Create Table client_master

sql

CREATE TABLE client_master (
    client_no VARCHAR2(6),
    name VARCHAR2(20),
    city VARCHAR2(15),
    state VARCHAR2(15),
    pincode NUMBER(6),
    bal_due NUMBER(10,2)
);

📥 Q2: Insert Data

sql

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0001', 'Ivan', 'Bombay', 400054, 'Maharashtra', 15000);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0002', 'Vandana', 'Madras', 780001, 'Tamilnadu', 0);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0003', 'Pramada', 'Bombay', 400057, 'Maharashtra', 5000);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0004', 'Basu', 'Bombay', NULL, 'Maharashtra', 0);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0005', 'Ravi', 'Delhi', 100001, NULL, 2000);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0006', 'Rukmini', 'Bombay', 400050, 'Maharashtra', 0);

🔍 Queries

sql

-- 1. Find names of all clients
SELECT name FROM client_master;

-- 2. List names and cities
SELECT name, city FROM client_master;

-- 3. Clients located in Bombay
SELECT * FROM client_master WHERE city = 'Bombay';

-- 4. Info for client_no 0001 and 0002
SELECT * FROM client_master WHERE client_no IN ('0001', '0002');

-- 5. Clients with balance due > 5000
SELECT * FROM client_master WHERE bal_due > 5000;

-- 6. Clients from Bombay, Delhi or Madras
SELECT * FROM client_master WHERE city IN ('Bombay', 'Delhi', 'Madras');

-- 7. Clients not from Maharashtra
SELECT * FROM client_master WHERE state != 'Maharashtra' OR state IS NULL;

📊 Expected Output Samples:

Query 3 Result (Clients in Bombay):

plaintext

CLIENT_NO NAME     CITY    STATE        PINCODE   BAL_DUE
0001      Ivan     Bombay  Maharashtra  400054    15000
0003      Pramada  Bombay  Maharashtra  400057    5000
0004      Basu     Bombay  Maharashtra  NULL      0
0006      Rukmini  Bombay  Maharashtra  400050    0

Query 4 Result (Client 0001 & 0002):

plaintext

CLIENT_NO NAME     CITY    STATE        PINCODE   BAL_DUE
0001      Ivan     Bombay  Maharashtra  400054    15000
0002      Vandana  Madras  Tamilnadu    780001    0

Query 7 Result (Not from Maharashtra):

plaintext

CLIENT_NO NAME       CITY       STATE         PINCODE   BAL_DUE
0002             Vandana  Madras  Tamilnadu  780001       0
0005            Ravi           Delhi       NULL           100001      2000