CREATE TABLE S1_123 (
StudentID INT,
Name VARCHAR(50) NOT NULL
);
insert into s1_123 values(1,’aaa’);
select * from s1_123;
alter table s1_123 add(email varchar2(100) unique);
alter table s1_123 modify(StudentID Int Primary Key);
insert into s1_123 values(2,’aaa’,’123@gmail.com’);
desc s1_123;
insert into s1_123 values(3,’bbb’,’12345@gmail.com’);
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
StudentID INT,
FOREIGN KEY (StudentID)
REFERENCES S1_123(StudentID)
);
select * from s1_123;
select name,studentid from s1_123;
select studentid from s1_123;
select * from s1_123 where studentid=1;
select * from s1_123 where name=’aaa’;
select name from s1_123 where studentid=2;
rename s1_123 to s1_1;
select * from s1_1;
truncate table s1_1;
select * from stud;
truncate table stud;
desc stud;
TRUNCATE TABLE Stud;
drop table stud;
select * from student;
delete from student where id=1;
delete from student where city=’rajkot’;
select * from stud_2644;
select * from emp80;
update emp80 set sal=8000 where empno=7839;
update emp80 set ename=’mital’ where deptno=10;
SELECT * FROM emp80
ORDER BY eName ASC;
SELECT ename, COUNT() FROM emp80 GROUP BY ename; SELECT ename, COUNT()
FROM emp80
GROUP BY ename
HAVING COUNT(*) > 1;
CREATE TABLE student (
student_id NUMBER PRIMARY KEY,
name VARCHAR2(30),
dept_id NUMBER,
CONSTRAINT fk_dept
FOREIGN KEY (dept_id)
REFERENCES department(dept_id)
);
CREATE TABLE department (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(30)
);
INSERT INTO department VALUES (1, ‘Computer’);
INSERT INTO department VALUES (2, ‘IT’);
INSERT INTO department VALUES (3, ‘Commerce’);
INSERT INTO department VALUES (4, ‘Mechanical’);
INSERT INTO student VALUES (101, ‘Amit’, 1);
INSERT INTO student VALUES (102, ‘Neha’, 2);
INSERT INTO student VALUES (103, ‘Ravi’, 3);
INSERT INTO student VALUES (104, ‘Pooja’, 2);
COMMIT;
SELECT s.student_id,
s.name,
d.dept_name
FROM student s
INNER JOIN department d
ON s.dept_id = d.dept_id;
SELECT s.student_id,
s.name,
d.dept_name
FROM student s
LEFT OUTER JOIN department d
ON s.dept_id = d.dept_id;
SELECT s.student_id,
s.name,
d.dept_name
FROM student s
RIGHT OUTER JOIN department d
ON s.dept_id = d.dept_id;
SELECT s.student_id,
s.name,
d.dept_name
FROM student s
FULL OUTER JOIN department d
ON s.dept_id = d.dept_id;
drop table department;
select * from department;
INSERT INTO department VALUES (1, ‘Computer’);
INSERT INTO department VALUES (2, ‘IT’);
INSERT INTO department VALUES (3, ‘Commerce’);
INSERT INTO department VALUES (4, ‘Mechanical’);
select * from department where dept_id=2 or dept_name=’Computer’;
SELECT * FROM department WHERE dept_id > ANY (SELECT dept_name FROM department WHERE dept_id = 4);
SELECT * FROM department WHERE dept_id BETWEEN 1 AND 3;
SELECT * FROM department WHERE dept_id IN (1, 2, 30);
SELECT * FROM department WHERE NOT dept_id = 2;
SELECT * FROM department WHERE dept_name LIKE ‘C_m%’;
CREATE TABLE Products (
ProductID NUMBER(10) PRIMARY KEY,
ProductName VARCHAR2(100) NOT NULL,
Price NUMBER(10,2) NOT NULL
);
INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1, ‘Chais’, 18.00);
INSERT INTO Products (ProductID, ProductName, Price)
VALUES (2, ‘Chang’, 19.00);
INSERT INTO Products (ProductID, ProductName, Price)
VALUES (3, ‘Aniseed Syrup’, 10.00);
INSERT INTO Products (ProductID, ProductName, Price)
VALUES (4, ‘Chef Anton”s Cajun Seasoning’, 22.00);
COMMIT;
select * from Products;
SELECT *
FROM Products
WHERE ProductID = ANY (
SELECT ProductID
FROM Products
WHERE Price >= 18
);