notes-shivam

SQL commands

2 min read

BASIC SQL COMMANDS

  • CREATE DB
CREATE DATABASE myDB;
  • CREATE A TABLE
USE myDB;
 
CREATE TABLE emp(
	empID INT PRIMARY KEY,
	firstName VARCHAR(25) NOT NULL,
	lastName VARCHAR(25),
	age INT
)
  • INSERT INTO TABLE
INSERT INTO emp
VALUES (1, 'Raj', 'Kapoor', 95),
	   (2, 'Ram', 'Khanna', 75),
	   (3, 'Aman', 'Khanna', 75),
	   (2, 'Salman', 'Khan', 65);
INSERT INTO emp(firstName, lastName)
VALUES ('ajay', 'sharma');
SELECT*FROM emp
WHERE empID != 1;
SELECT*
FROM emp
WHERE age IS NOT NULL;
UPDATE emp
SET age = 20
WHERE empID = 1;
DELETE FROM emp
WHERE salary < 10000;
SET AUTOCOMMIT = OFF;
ALTER TABLE emp 
DROP COLUMN age;
ALTER TABLE emp
MODIFY email VARCHAR(100)
AFTER lastName;

AUTOCOMMIT

  • turn off auto commit so we manually manage save states like a game i. e save and rollback from a checkpoint;
SET AUTOCOMMIT = OFF;
  • manual commit command
COMMIT;
  • restore to previous safe-point
ROLLBACK;

DATE AND TIME

  • CURRENT_DATE() AND CURRENT_TIME are SQL functions which return the current date and time
  • NOW( ) returns both
CREATE TABLE datetime(myDate DATE, )
 
INSERT INTO dateTime
VALUES(
	CURRENT_DATE(),
	CURRENT_TIME(),
	NOW()
);

CONSTRAINT

UNIQUE

CREATE TABLE products(
product_ID INT PRIMARY KEY,
productName VARCHAR(25) UNIQUE,
)
ALTER TABLE products
ADD CONSTRAINT
UNIQUE(productName);

NOT NULL

CREATE TABLE product(
productID INT PRIMARY KEY,
productName VARCHAR(25),
price DECIMAL(4,2) NOT NULL
);
ALTER TBALE products
MODIFY price DECIMAL(4,2) NOT NULL;

CHECK

add the check constraint while creating a table

CREATE TABLE emp(
	empid INT PRIMARY KEY,
	firstName VARCHAR(50),
	lastName VARCHAR(50),
	hourlyPay DECIMAL(5,2),
	hireDate DATE,
	CONSTRAINT checkHourlyRate CHECK (hourlyPay >= 10.00)
);

or , to add the check constraint after creating the table

ALTER TBALE emp
ADD CONSTRAINT checkHourlyPay CHECK (hourlyPay >= 5.99);
  • TO DELETE THE CHECK CONSTRAINT
ALTER TBALE emp
DROP CHECK checkHourlyPay;

DEFAULT

  • to add default value to columns
CREATE TABLE products(
	productID INT,
	productName VARCHAR(25),
	price DECIMAL(5,2) DEFAULT 0
);
  • TO ADD constraint to already existing table
ALTER TABLE products
ALTER price SET DEFAULT 10.00;

e. g

CREATE TABLE transactions(
	tn_ID INT,
	amount DECIMAL(5,2),
	tn_date DATETIME DEFAULT NOW;
)
CRETE TABLE emp (empid int, empname varchar);
CREATE TABLE emp

Graph View