ARD

1. DDL, DML AND TCL COMMANDS

CODING: TABLE CREATION
CREATE TABLE EMP_CS051(emp_name VARCHAR (20), emp_id INT, emp_qua VARCHAR (20), job role VARCHAR (20), salary NUMBER (8));
   TABLE CREATED
INSERTION:
INSERT INTO EMP_CS051 VALUES ('GAJA','101','BSC CS','BUSINESS ANALYST','20000');
   1 ROW INSERTED
INSERT INTO EMP_CS051 VALUES ('MAHA','102','BSC CS','SOFTWARE ENGINEER','25000');
   1 ROW INSERTED
INSERT INTO EMP_CS051 VALUES ('UKA','103','BSC CS','JUNIOR ENGINEER','23000');
   1 ROW INSERTED
INSERT INTO EMP_CS051 VALUES ('KIRUTHI','104','BSC CS','JUNIOR ASSISTANT','18000');
   1 ROW INSERTED
INSERT INTO EMP_CS051 VALUES ('KAVIYA','105','BSC CS','SYSTEM ANALYST','19000');
   1 ROW INSERTED
INSERT INTO EMP_CS051 VALUES ('SHARMI','106','BSC CS','SENIOR ENGINEER','26000');
   1 ROW INSERTED
SELECT*FROM EMP_CS051;
ALTER:
ALTER TABLE EMP_CS051 ADD PH_NUM NUMBER (10);
UPDATE:
UPDATE EMP_CS051 SET SALARY='24000' WHERE EMP_ID='101';
TRUNCATE:
TRUNCATE TABLE EMP_CS051;
TABLE TRUNCATED
DROP:
DROP TABLE EMP_CS051;
TABLE DROPPED
COMMIT:
COMMIT;
COMMIT COMPLETE
ROLLBACK:
ROLLBACK;
RENAME:
RENAME EMP_CS051 TO EMPLOYEE_51;
TABLE RENAMED

2.DATA AND BUILT IN FUNCTIONS IN SQL

CODING: TABLE CREATION
CREATE TABLE STUDENT_05 (Name VARCHAR (20), Regnum VARCHAR (8), Mark_1 NUMBER (3), Mark_2 NUMBER (3), Mark_3 NUMBER (3), Mark_4 NUMBER (3), Mark_5 NUMBER (3));
INSERTION:
INSERT INTO STUDENT_05 VALUES('GAJA','232CS101',99,89,77,68,69);
   1 ROW INSERTED
INSERT INTO STUDENT_05 VALUES('UKA','232CS102',68,76,75,74,70);
   1 ROW INSERTED
INSERT INTO STUDENT_05 VALUES('MAHA','232CS103',99,92,87,88,84);
   1 ROW INSERTED
INSERT INTO STUDENT_05 VALUES('KIRUTHI','232CS104',93,97,80,60,67);
   1 ROW INSERTED
SELECT*FROM STUDENT_05;

SUM:
SELECT Name, (Mark_1+Mark_2+Mark_3+Mark_4+Mark_5) FROM STUDENT_05;
AVERAGE:
SELECT Name, (Mark_1+Mark_2+Mark_3+Mark_4+Mark_5)/5 AS AVGMarks FROM STUDENT_05;
ROUND:
SELECT Name, Round((Mark_1+Mark_2+Mark_3+Mark_4+Mark_5)/5) AS AVGMarks FROM STUDENT_05;
COUNT:
SELECT COUNT(Name) FROM STUDENT_05;
MAX:
SELECT Max (Mark_1) FROM STUDENT_05;
MIN:
SELECT Min (Mark_4) FROM STUDENT_05;
GROUP BY HAVING:
SELECT Name FROM STUDENT_05 where Mark_1>90 GROUP BY Name Having sum (Mark_1)>70;
UPPER:
SELECT UPPER(Name) FROM STUDENT_05;
LOWER:
SELECT LOWER(Name) FROM STUDENT_05;
LENGTH:
SELECT LENGTH(Name)FROM STUDENT_05;
CONCAT:
SELECT CONCAT (Name, Mark_1) FROM STUDENT_05;
SUBSTR:
SELECT SUBSTR(Name,2) FROM STUDENT_05;
UPDATE:
UPDATE STUDENT_05 SET Name= REPLACE(Name,'GAJA','LAKSH');

3.RELATIONAL ALGEBRA FOR A SET OF RELATIONS
CODING: TABLE CREATION
CUSTOMER_51
CREATE TABLE CUSTOMER_51(C_ID VARCHAR (8), C_NAME VARCHAR (20), PH_NUM NUMBER (10));
INSERTION:
INSERT INTO CUSTOMER_51 VALUES('001','LAKSHU',9088907564);
   1 ROW INSERTED
INSERT INTO CUSTOMER_51 VALUES('002','NEHA',9588907561);
   1 ROW INSERTED
INSERT INTO CUSTOMER_51 VALUES('003','MAHA',9808907563);
   1 ROW INSERTED
INSERT INTO CUSTOMER_51 VALUES('004','NAR',8180907562);
   1 ROW INSERTED
SELECT * FROM CUSTOMER_51;
STAFF_51
CREATE TABLE STAFF_51(ID VARCHAR (8), NAME VARCHAR (20), PLACE VARCHAR (10));
INSERTION:
INSERT INTO STAFF_51 VALUES('001','LAKS','CBE');
1 ROW INSERTED
INSERT INTO STAFF_51 VALUES('002','LISA','OOTY');
   1 ROW INSERTED
INSERT INTO STAFF_51 VALUES('003','LEKA','NILIGIRIS');
   1 ROW INSERTED
SELECT * FROM STAFF_51;
1.INNER JOIN
SELECT CUSTOMER_51.C_NAME, CUSTOMER_51.C_ID, STAFF_51.ID
FROM CUSTOMER_51
INNER JOIN STAFF_51
ON CUSTOMER_51.C_ID=STAFF_51.ID;
2.CROSS JOIN
SELECT CUSTOMER_51.C_NAME, CUSTOMER_51.C_ID, STAFF_51.NAME
FROM CUSTOMER_51
CROSS JOIN STAFF_51;
3.LEFT OUTER JOIN
SELECT CUSTOMER_51.C_NAME, CUSTOMER_51.C_ID, STAFF_51.ID, STAFF_51.NAME, CUSTOMER_51.PH_NUM
FROM CUSTOMER_51
LEFT OUTER JOIN STAFF_51
ON CUSTOMER_51.C_ID=STAFF_51.ID;
4.FULL OUTER JOIN
SELECT CUSTOMER_51.C_NAME, CUSTOMER_51.C_ID, CUSTOMER_51.PH_NUM, STAFF_51.NAME, STAFF_51. PLACE
FROM CUSTOMER_51
FULL OUTER JOIN STAFF_51
ON CUSTOMER_51.C_ID=STAFF_51.ID;
5.SELF JOIN
SELECT CUSTOMER_51.C_NAME, CUSTOMER_51.C_ID, CUSTOMER_51.PH_NUM
FROM CUSTOMER_51
SELF JOIN CUSTOMER_51
ON CUSTOMER_51.C_ID=CUSTOMER_51.C_ID;
6.UNION
SELECT*FROM CUSTOMER_51
LEFT JOIN STAFF_51
ON CUSTOMER_51.C_NAME=STAFF_51.NAME
UNION
SELECT *FROM CUSTOMER_51
RIGHT JOIN STAFF_51
ON CUSTOMER_51.C_NAME=STAFF_51.NAME;

5.IMPLEMENTATION OF VIEWS

CODING: TABLE_1
CREATE TABLE LAKS_5(DEPNAME VARCHAR (15), STAFFID NUMBER (2), PLACE VARCHAR (10));
INSERT INTO LAKS_5 VALUES ('CS','01','CBE');
1 ROW INSERTED
INSERT INTO LAKS_5 VALUES ('MATHS','02','OOTY');
1 ROW INSERTED
INSERT INTO LAKS_5 VALUES ('PHYSICS','03','NILIGIRIS');
1 ROW INSERTED
SELECT * FROM LAKS_5;
TABLE_2
CREATE TABLE D_5(COLLEGENAME VARCHAR (8), STAFFID NUMBER (2), SALARY NUMBER (10));
INSERT INTO D_5 VALUES('NGP','01','22000');
1 ROW INSERTED
INSERT INTO D_5 VALUES('NGPIT','02','25000');
1 ROW INSERTED
INSERT INTO D_5 VALUES('KMCH','03','26000');
1 ROW INSERTED
INSERT INTO D_5 VALUES('PSG','04','27000');
1 ROW INSERTED
SELECT * FROM D_5;
VIEW:
CREATE VIEW G_8 AS
SELECT LAKS_5. DEPNAME, LAKS_5. STAFFID, LAKS_5. PLACE, D_5. COLLEGENAME, D_5. SALARY
FROM LAKS_5, D_5
WHERE D_5. SALARY>22000;
SELECT*FROM G_8;

6.IMPLEMENTATION OF CURSORS

CODING: TABLE_CREATION
CREATE TABLE SPORTS_1(ID NUMBER, NAME VARCHAR (20), AGE NUMBER (5), TYPE_OF_SPORT VARCHAR2(20));
INSERTION:
INSERT INTO SPORTS_1 VALUES('101','MAHA',21,'VOLLEYBALL');
1 ROW INSERTED
INSERT INTO SPORTS_1 VALUES('102','LAKS',22,'THROWBALL');
1 ROW INSERTED
INSERT INTO SPORTS_1 VALUES('103','UKA',21,'BASKETBALL');
1 ROW INSERTED
INSERT INTO SPORTS_1 VALUES('104','NEHA',21,'HOCKEY');
1 ROW INSERTED
SELECT * FROM SPORTS_1;
DECLARE
    S_ID SPORTS_1.ID%Type;
    S_NAME SPORTS_1.NAME%Type;
    S_AGE SPORTS_1.AGE%Type;
    CURSOR S_SPORTS_1 IS
    SELECT ID, NAME, AGE FROM SPORTS_1;
BEGIN
    OPEN S_SPORTS_1;
    LOOP
    FETCH S_SPORTS_1 INTO S_ID, S_NAME, S_AGE;
        EXIT WHEN S_SPORTS_1%NOTFOUND;
        dbms_output.put_line (S_ID || '' ||S_NAME ||S_AGE);
    END LOOP;
    CLOSE S_SPORTS_1;
END;
/


7.IMPLEMENTATION OF TRIGGERS
CODING: TABLE CREATION
CREATE TABLE SPORTS_8 (ID NUMBER, NAME VARCHAR (20), AGE NUMBER (5), TYPE_OF_SPORT VARCHAR2(20));
INSERTION:
INSERT INTO SPORTS_8 VALUES('101','MAHA',21,'VOLLEYBALL');
1 ROW INSERTED
INSERT INTO SPORTS_8 VALUES('102','LAKS',22,'THROWBALL');
1 ROW INSERTED
INSERT INTO SPORTS_8 VALUES('103','UKA',21,'BASKETBALL');
1 ROW INSERTED
INSERT INTO SPORTS_8 VALUES('104','NEHA',21,'HOCKEY');
1 ROW INSERTED
SELECT*FROM SPORTS_8;
TRIGGER CREATION
CREATE OR REPLACE TRIGGER sport_8trigger BEFORE DELETE OR INSERT OR UPDATE ON SPORTS_8 FOR EACH ROW WHEN (NEW.ID>0)
DECLARE
    DIFF NUMBER;
BEGIN
    DIFF: =: NEW.AGE-: OLD.AGE;
    dbms_output.put_line ('OLD.AGE:'||: OLD.AGE);
    dbms_output.put_line ('NEW.AGE:'||: NEW.AGE);
    dbms_output.put_line ('AGE DIFFERENCE:'||DIFF);
END;
/
Trigger SPORT_8TRIGGER compiled
INSERT INTO SPORTS_8 VALUES (105,'NEHA',23,'CRICKET');
1 ROW INSERTED
UPDATE
UPDATE SPORTS_8 SET AGE=AGE+2 WHERE ID=101;
   1 ROW UPDATED


8.PL/SQL PROCEDURES AND FUNCTIONS
CODING:
DECLARE
      a number;
      b number;
      c number;
PROCEDURE findMin (x IN number, y IN number, z OUT number) IS
BEGIN
     IF x<y THEN
         z: =x;
     ELSE
        z: =y;
     END IF;
END;
BEGIN
     a: =20;
     b: =18;
     findMin (a, b, c);
     dbms_output.put_line ('Minimum of (20,18):'||c);
END;
/

9.ERROR AND EXCEPTIONAL HANDLING

CODING:
DECLARE
     a int: =8;
     b int: =0;
     div int;
BEGIN
    div: =a/b;
    dbms_output.put_line ('The result after division is'||div);
EXCEPTION
WHEN zero_divide THEN
    dbms_output.put_line ('Dividing by zero');
    dbms_output.put_line ('VALUE OF NUMERATOR IS'|| a);
    dbms_output.put_line ('VALUE OF DENOMINATOR IS'|| b);
END;


11.IMPLEMENTATION OF TRANSACTION MANAGEMENT

CODING: TABLE_CREATION
CREATE TABLE SPORTS_08(ID NUMBER, NAME VARCHAR (20), AGE NUMBER (5), TYPE_OF_SPORT VARCHAR2(20));CH
    TABLE CREATED
INSERT INTO SPORTS_08 VALUES('101','D',23,'VOLLEYBALL');
 1 ROW INSERTED
INSERT INTO SPORTS_08 VALUES('102','LAKS',22,'THROWBALL');
 1 ROW INSERTED
INSERT INTO SPORTS_08 VALUES('103','NAR',21,'BASKETBALL');
 1 ROW INSERTED
INSERT INTO SPORTS_08 VALUES('104','NEHA',21,'HOCKEY');
1 ROW INSERTED
SELECT * FROM SPORTS_08;
COMMIT:
COMMIT;
SELECT * FROM SPORTS_08;
DELETE:
DELETE FROM SPORTS_08 WHERE NAME='NEHA';
ROLLBACK
ROLLBACK;
SELECT * FROM SPORTS_08;

12.DB CONNECTIVITY

connect.php
<?php
$name = $_POST['name'];
$rollno = $_POST['rollno'];
$age = $_POST['age'];
$city= $_POST['city'];
$grade = $_POST['grade'];
// Database connection
$conn = new mysqli('localhost','root','','test');
if($conn->connect_error){
echo "$conn->connect_error";
die("Connection Failed : ". $conn->connect_error);
} else {
$stmt = $conn->prepare("insert into laks(name, rollno, age, city, grade) values(?, ?, ?, ?, ?)");
$stmt->bind_param("ssssi", $name, $rollno, $age, $city, $grade);
$execval = $stmt->execute();
echo $execval;
echo "Registration successfully...";
$stmt->close();
$conn->close();
}
?>


index.html

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Student Information Form</title>
</head>
<body>

<h2>Student Information Form</h2>

 <form action="connect.php" method="post">
  <label for="name">Name:</label><br>
  <input type="text" id="name" name="name" required><br>
 
  <label for="rollno">Roll Number:</label><br>
  <input type="text" id="rollno" name="rollno" required><br>
 
  <label for="age">Age:</label><br>
  <input type="number" id="age" name="age" required><br>
 
  <label for="city">City:</label><br>
  <input type="text" id="city" name="city" required><br>
 
  <label for="grade">Grade:</label><br>
  <select id="grade" name="grade" required>
    <option value="">Select Grade</option>
    <option value="O">O</option>
    <option value="A">A</option>
    <option value="B">B</option>
    <option value="C">C</option>
    <option value="D">D</option>
  </select><br><br>
 
  <input type="submit" value="Submit">
</form>

</body>
</html>

Popular posts from this blog

Python programs

 1) #Using intersection() list1=[10,20,30,40,50,'geeks'] list2=[10,20,'geeks'] if(set(list1).intersection(set(list2))==set(list2)):     print("Sublist exist") else:     print("Sublist not exist") #Getting i/p from user l1=list(input("")) print("List1:",l1) l2=list(input("")) print("List2:",l2) flag=False for i in range(len(l1)-len(l2)+1):     if l1[i:i+len(l2)]==l2:         flag=True         break print("Is sublist present in list....",flag) #Using for loop list1=[10,20,30,40,50] list2=[10,20] list=False for i in range(0,len(list1)):     j=0     while((i+j)<len(list1) and j<len(list2) and list1[i+j] == list2[j]):         j+=1     if j==len(list2):         list=True         break if(list):     print("Sublist exist") else:     print("Sublist not exist") #Using subset() list1=[10,20,30,40,50] list2=[10...
Read more