SQL
Ques1. Create a table with the name ‘customer’ and having following data items.
Cust no char (2)
Lastname char (15)
First name char (15)
Street varchar (15)
City char (15)
Credit limit number (7,2)
Balance number (7,2)
Code:
CREATE TABLE Customer (
custno CHAR(4) PRIMARY KEY,
lastname VARCHAR(30) NOT NULL,
firstname VARCHAR(30) NOT NULL,
street VARCHAR(50),
city VARCHAR(30) );
INSTER INTO Customer (custno, lastname, firstname, street, city) VALUES
('C001','Singh','Aman','MG Road','Agra'),
('C002','Kumar','Priya','Park St','Mumbai'),
('C003','Patel','Rakesh','1st Cross','Ahmedabad'),
('C004','Sharma','Suresh','Green Ave','Delhi'),
('C005','Johnson','Alison','Lake Rd','Alleppey'),
('C006','Robinson','Jason','Hill St','Amritsar');
OUTPUT:
Run the following queries:
● List Customer Id of all customers.
Code:
SELECT custno FROM Customer;
Output:
● Include the field ‘TelNo’ in the customer table.
Code:
ALTER TABLE Customer ADD COLUMN TelNo VARCHAR(15);
Output:
● List customer details who live in city beginning with city name ‘A’
Code:
SELECT * FROM Customer
WHERE city LIKE 'A%';
Output:
● List Customer No. of those whose name has pattern ‘son’
Code:
SELECT custno, firstname, lastname FROM Customer
WHERE firstname LIKE '%son%' OR lastname LIKE '%son%';
Output:
● Display details of Customers whose last name starts with ‘S’
Code:
SELECT * FROM Customer
WHERE lastname LIKE 'S%';
Output:
● Display all customer names in alphabetical order.
Code:
SELECT * FROM Customer
ORDER BY lastname ASC;
Output: