Queries

Create a new table

CREATE TABLE IF NOT EXISTS employee ( emp_id SERIAL PRIMARY KEY, -- AUTO_INCREMENT integer, as primary key emp_name VARCHAR(50) NOT NULL, emp_salary NUMERIC(9,2) NOT NULL );

// Creates a new table (SQL)

Display table

\d employee Table "public.employee" Column | Type | Modifiers ------------+-----------------------+----------------------------------------------------------- emp_id | integer | not null default nextval('employee_emp_id_seq'::regclass) emp_name | character varying(50) | not null emp_salar | numeric(9,2) | not null Indexes: "employee_pkey" PRIMARY KEY, btree (emp_id)

// Display table (psql)

Insert query

INSERT INTO employee (emp_name, emp_salary) VALUES ('John', 5000), ('Jack', 4568.0), ('Robert',7500.50);

// Insert records into table (SQL)

Conditional select query

select * from employee where emp_salary >= 5000; emp_id | emp_name | emp_salary --------+----------+------------ 1 | John | 5000.00 3 | Robert | 7500.50 (2 rows)

// Select data based on filter condition (e.g. emp_salary >= 5000) (SQL)

Conditional update query (Safe Update);

BEGIN; update employee set emp_salary = 6000 where emp_name = 'John'; COMMIT;

// Update record based on a condition (e.g. Update emp_salary for employee ‘John’) (SQL)

Records are not committed inside database unless you issue a commit. Updates can be undone as well if you issue ROLLBACK command instead of COMMIT.

Alter table

alter table employee add column dept_id integer; ALTER TABLE

// Alter table to add a new column (e.g. add dept_id in employee table) (SQL)

alter table employee drop column dept_id; ALTER TABLE

// Alter table to drop column (e.g. drop dept_id from employee table) (SQL)

Truncate table

truncate only employee; TRUNCATE TABLE

// Truncate table employee (SQL)

Truncating a table is a quick way to remove records from a table because it does not need to scan the table. Truncate is a not logged activity inside database. Truncating a table is also a lot easier than dropping the table and recreating it.

This is safe to use “ONLY” keyword so that you don’t accidentally truncate dependant/child tables.

truncate only tableA, tableB; TRUNCATE TABLE

// Truncate multiple tables at once (SQL)

Conditional delete query (Rollback)

BEGIN; delete from employee where emp_id = 2; select * from employee where emp_id = 2; emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id --------+----------+---------+------------+------------- (0 rows) ROLLBACK; select * from employee where emp_id = 2; emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id --------+----------+---------+------------+------------- 2 | Rohit | 1234 | 5000.00 | 1 (1 row)

// Delete from employee based on filter condition (emp_id = 1) & then Rollback transaction (SQL)

Parameterized statements

PREPARE myplan (int) AS SELECT * FROM employee where emp_id = $1;

// Creates a parameterized statement and stores query access plan on server (SQL)

EXECUTE myplan(2); emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id --------+----------+---------+------------+------------- 2 | Rohit | 1234 | 5000.00 | 1 (1 row)

// Executes query as per stored access plan providing value for parameters (e.g. $1=2) (SQL)

Parameterized statements are an effective way to write queries that use the same access plan irrespective of run time values provided. Applications like java use the JDBC API to create parameterized statements like this:

Int emp_id = 2; con = getDBConnection(); // get database connection PreparedStatement pstmt = con.prepareStatement("select * from employee where emp_id = ?"); // prepare parameterized statement pstmt.setInt(1, emp_id); // provide runtime value pstmt.execute(); // execute query