CIAL!

2. Experiment 2:

SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> SELECT 16 + 9 AS Result FROM DUAL;

    RESULT
----------
        25

SQL> SELECT 119 - 5 AS Result FROM DUAL;

    RESULT
----------
       114

SQL> SELECT 12 * 4 AS Result FROM DUAL;

    RESULT
----------
        48

SQL> SELECT 27 / 3 AS Result FROM DUAL;

    RESULT
----------
         9

SQL> SELECT 22 % 5 AS Result FROM DUAL;
SELECT 22 % 5 AS Result FROM DUAL
          *
ERROR at line 1:
ORA-00911: invalid character


SQL> SELECT 22 | 5 AS Result FROM DUAL;
SELECT 22 | 5 AS Result FROM DUAL
          *
ERROR at line 1:
ORA-00996: the concatenate operator is ||, not |


SQL> SELECT (22 % 5) AS Result FROM DUAL;
SELECT (22 % 5) AS Result FROM DUAL
           *
ERROR at line 1:
ORA-00911: invalid character


SQL> SELECT 22 % 5 AS Result FROM DUAL;
SELECT 22 % 5 AS Result FROM DUAL
          *
ERROR at line 1:
ORA-00911: invalid character


SQL> SELECT MOD(22,5) FROM DUAL;

 MOD(22,5)
----------
         2

SQL> CREATE TABLE dummy_table (
  2    id NUMBER,
  3    name VARCHAR2(50),
  4    age NUMBER
  5  );

Table created.

SQL>
SQL> INSERT INTO dummy_table (id, name, age) VALUES (1, 'John', 25);

1 row created.

SQL> INSERT INTO dummy_table (id, name, age) VALUES (2, 'Jane', 30);

1 row created.

SQL> INSERT INTO dummy_table (id, name, age) VALUES (3, 'Alice', 22);

1 row created.

SQL> INSERT INTO dummy_table (id, name, age) VALUES (4, 'Bob', 40);

1 row created.

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age = 30;

        ID NAME
---------- --------------------------------------------------
         2 Jane

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age <> 25;

        ID NAME
---------- --------------------------------------------------
         2 Jane
         3 Alice
         4 Bob

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age > 25;

        ID NAME
---------- --------------------------------------------------
         2 Jane
         4 Bob

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age < 30;

        ID NAME
---------- --------------------------------------------------
         1 John
         3 Alice

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age >= 30;

        ID NAME
---------- --------------------------------------------------
         2 Jane
         4 Bob

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age <= 25;

        ID NAME
---------- --------------------------------------------------
         1 John
         3 Alice

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age IS NULL;

no rows selected

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age IS NOT NULL;

        ID NAME
---------- --------------------------------------------------
         1 John
         2 Jane
         3 Alice
         4 Bob

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age BETWEEN 25 AND 35;

        ID NAME
---------- --------------------------------------------------
         1 John
         2 Jane

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE age BETWEEN 25 AND 35;

        ID NAME
---------- --------------------------------------------------
         1 John
         2 Jane

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE name LIKE 'J%';

        ID NAME
---------- --------------------------------------------------
         1 John
         2 Jane

SQL> SELECT id, name
  2  FROM dummy_table
  3  WHERE name IN ('John', 'Alice');

        ID NAME
---------- --------------------------------------------------
         1 John
         3 Alice

SQL> DROP TABLE dummy_table;

Table dropped.

SQL> -- Creating the dummy_table
SQL> CREATE TABLE dummy_table (
  2    id NUMBER,
  3    name VARCHAR2(50),
  4    age NUMBER
  5  );

Table created.

SQL>
SQL> -- Inserting sample data
SQL> INSERT INTO dummy_table (id, name, age) VALUES (1, 'John', 25);

1 row created.

SQL> INSERT INTO dummy_table (id, name, age) VALUES (2, 'Jane', 30);

1 row created.

SQL> INSERT INTO dummy_table (id, name, age) VALUES (3, 'Alice', 22);

1 row created.

SQL> INSERT INTO dummy_table (id, name, age) VALUES (4, 'Bob', 40);

1 row created.

SQL> INSERT INTO dummy_table (id, name, age) VALUES (5, 'Eve', 35);

1 row created.

SQL> SELECT MIN(age) AS min_age
  2  FROM dummy_table;

   MIN_AGE
----------
        22

SQL> SELECT MAX(age) AS max_age
  2  FROM dummy_table;

   MAX_AGE
----------
        40

SQL> SELECT SUM(age) AS total_age
  2  FROM dummy_table;

 TOTAL_AGE
----------
       152

SQL> SELECT AVG(age) AS average_age
  2  FROM dummy_table;

AVERAGE_AGE
-----------
       30.4

SQL> SELECT COUNT(*) AS total_rows
  2  FROM dummy_table;

TOTAL_ROWS
----------
         5

SQL>
SQL> SELECT COUNT(age) AS non_null_count
  2  FROM dummy_table;

NON_NULL_COUNT
--------------
             5

SQL> SELECT VARIANCE(age) AS age_variance
  2  FROM dummy_table;

AGE_VARIANCE
------------
        53.3

SQL> SELECT STDDEV(age) AS age_stddev
  2  FROM dummy_table;

AGE_STDDEV
----------
 7.3006849

SQL> SELECT SQRT(25) AS square_root
  2  FROM DUAL;

SQUARE_ROOT
-----------
          5

SQL> SELECT POWER(2, 3) AS power_result -- 2 raised to the power of 3 (2^3)
  2  FROM DUAL;

POWER_RESULT
------------
           8

SQL> SELECT POW(2, 3) AS power_result -- 2 raised to the power of 3 (2^3)
  2  FROM DUAL;
SELECT POW(2, 3) AS power_result -- 2 raised to the power of 3 (2^3)
       *
ERROR at line 1:
ORA-00904: "POW": invalid identifier


SQL> SELECT ABS(-10) AS absolute_value
  2  FROM DUAL;

ABSOLUTE_VALUE
--------------
            10

SQL> SELECT CEIL(4.7) AS ceiling_value
  2  FROM DUAL;

CEILING_VALUE
-------------
            5

SQL> SELECT CEILING(4.7) AS ceiling_value
  2  FROM DUAL;
SELECT CEILING(4.7) AS ceiling_value
       *
ERROR at line 1:
ORA-00904: "CEILING": invalid identifier


SQL> SELECT FLOOR(4.7) AS floor_value
  2  FROM DUAL;

FLOOR_VALUE
-----------
          4

SQL> SELECT ROUND(4.756, 2) AS rounded_value
  2  FROM DUAL;

ROUNDED_VALUE
-------------
         4.76

SQL> SELECT TRUNC(4.756, 2) AS truncated_value
  2  FROM DUAL;

TRUNCATED_VALUE
---------------
           4.75

SQL> SELECT EXP(2) AS exponential_value
  2  FROM DUAL;

EXPONENTIAL_VALUE
-----------------
        7.3890561

SQL> SELECT LN(10) AS natural_log
  2  FROM DUAL;

NATURAL_LOG
-----------
 2.30258509

SQL> SELECT LOG(100, 10) AS log_base_10 -- log base 10 of 100
  2  FROM DUAL;

LOG_BASE_10
-----------
         .5

SQL> SELECT SIN(0.5) AS sine_value
  2  FROM DUAL;

SINE_VALUE
----------
.479425539

SQL>
SQL> SELECT COS(0.5) AS cosine_value
  2  FROM DUAL;

COSINE_VALUE
------------
  .877582562

SQL>
SQL> SELECT TAN(0.5) AS tangent_value
  2  FROM DUAL;

TANGENT_VALUE
-------------
    .54630249

SQL> SELECT SIN(0.5) AS sine_value
  2  FROM DUAL;

SINE_VALUE
----------
.479425539

SQL>
SQL> SELECT COS(0.5) AS cosine_value
  2  FROM DUAL;

COSINE_VALUE
------------
  .877582562

SQL>
SQL> SELECT TAN(0.5) AS tangent_value
  2  FROM DUAL;

TANGENT_VALUE
-------------
    .54630249

SQL> SELECT ATN2(3, 4) AS arctangent_value
  2  FROM DUAL;
SELECT ATN2(3, 4) AS arctangent_value
       *
ERROR at line 1:
ORA-00904: "ATN2": invalid identifier


SQL> SELECT 1 / TAN(0.5) AS cotangent_value
  2  FROM DUAL;

COTANGENT_VALUE
---------------
     1.83048772

3. Experiment 3:

SQL> CONNECT
Enter user-name: SYSTEM
Enter password:
Connected.
SQL> CREATE TABLE Employee (
  2      emp_id INT,
  3      name VARCHAR(50) NOT NULL,
  4      city VARCHAR(50) NOT NULL,
  5      BP DECIMAL(10, 2) NOT NULL,
  6      HRA DECIMAL(10, 2) NOT NULL,
  7      DA DECIMAL(10, 2) NOT NULL,
  8      age INT NOT NULL,
  9      net_pay DECIMAL(10, 2) NOT NULL
 10  );

Table created.

SQL> INSERT INTO Employee (emp_id, name, city, BP, HRA, DA, age, net_pay)
  2  VALUES
  3      ('1', 'Ashwin Vaishnav', 'New Delhi', 50000.00, 10000.00, 8000.00, 30, 68000.00);

1 row created.

SQL> INSERT INTO Employee (emp_id, name, city, BP, HRA, DA, age, net_pay)
  2  VALUES
  3      ('2', 'Jane Smith', 'Los Angeles', 45000.00, 9000.00, 7000.00, 28, 61000.00);

1 row created.

SQL> INSERT INTO Employee (emp_id, name, city, BP, HRA, DA, age, net_pay)
  2  VALUES
  3  ('3', 'Karanjit Kaur', 'Bengaluru', 52000.00, 11000.00, 9000.00, 35, 72000.00);

1 row created.

SQL> INSERT INTO Employee (emp_id, name, city, BP, HRA, DA, age, net_pay)
  2  VALUES
  3  ('4', 'Bhaavna Singh', 'Punjab', 48000.00, 9500.00, 7500.00, 32, 65000.00);

1 row created.

SQL> INSERT INTO Employee (emp_id, name, city, BP, HRA, DA, age, net_pay)
  2  VALUES
  3  ('5', 'Aryan Agrawal', 'Chandigarh', 55000.00, 12000.00, 10000.00, 29, 77000.00);

1 row created.

SQL> select * from Employee;

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP        HRA
-------------------------------------------------- ---------- ----------
        DA        AGE    NET_PAY
---------- ---------- ----------
         1 Ashwin Vaishnav
New Delhi                                               50000      10000
      8000         30      68000

         2 Jane Smith
Los Angeles                                             45000       9000
      7000         28      61000

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP        HRA
-------------------------------------------------- ---------- ----------
        DA        AGE    NET_PAY
---------- ---------- ----------

         3 Karanjit Kaur
Bengaluru                                               52000      11000
      9000         35      72000

         4 Bhaavna Singh
Punjab                                                  48000       9500

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP        HRA
-------------------------------------------------- ---------- ----------
        DA        AGE    NET_PAY
---------- ---------- ----------
      7500         32      65000

         5 Aryan Agrawal
Chandigarh                                              55000      12000
     10000         29      77000


SQL> SELECT emp_id, name, city, BP, DA, HRA, age, (BP + DA + HRA) AS net_pay
  2  FROM Employee;

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------
         1 Ashwin Vaishnav
New Delhi                                               50000       8000
     10000         30      68000

         2 Jane Smith
Los Angeles                                             45000       7000
      9000         28      61000

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------

         3 Karanjit Kaur
Bengaluru                                               52000       9000
     11000         35      72000

         4 Bhaavna Singh
Punjab                                                  48000       7500

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------
      9500         32      65000

         5 Aryan Agrawal
Chandigarh                                              55000      10000
     12000         29      77000


SQL> SELECT SUM(BP + DA + HRA) AS total_salary_paid
  2  FROM Employee;

TOTAL_SALARY_PAID
-----------------
           343000

SQL> SELECT COUNT(*) AS total_employees
  2  FROM Employee;

TOTAL_EMPLOYEES
---------------
              5

SQL> SELECT MIN(BP + DA + HRA) AS min_salary, MAX(BP + DA + HRA) AS max_salary
  2  FROM Employee;

MIN_SALARY MAX_SALARY
---------- ----------
     61000      77000

SQL> SELECT name
  2  FROM Employee
  3  WHERE (BP + DA + HRA) = (SELECT MIN(BP + DA + HRA) FROM Employee)
  4  UNION ALL
  5  SELECT name
  6  FROM Employee
  7  WHERE (BP + DA + HRA) = (SELECT MAX(BP + DA + HRA) FROM Employee);

NAME
--------------------------------------------------
Jane Smith
Aryan Agrawal

SQL> -- Ascending order
SQL> SELECT emp_id, name, city, BP, DA, HRA, age, (BP + DA + HRA) AS net_pay
  2  FROM Employee
  3  ORDER BY (BP + DA + HRA) ASC;

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------
         2 Jane Smith
Los Angeles                                             45000       7000
      9000         28      61000

         4 Bhaavna Singh
Punjab                                                  48000       7500
      9500         32      65000

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------

         1 Ashwin Vaishnav
New Delhi                                               50000       8000
     10000         30      68000

         3 Karanjit Kaur
Bengaluru                                               52000       9000

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------
     11000         35      72000

         5 Aryan Agrawal
Chandigarh                                              55000      10000
     12000         29      77000


SQL>
SQL> -- Descending order
SQL> SELECT emp_id, name, city, BP, DA, HRA, age, (BP + DA + HRA) AS net_pay
  2  FROM Employee
  3  ORDER BY (BP + DA + HRA) DESC;

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------
         5 Aryan Agrawal
Chandigarh                                              55000      10000
     12000         29      77000

         3 Karanjit Kaur
Bengaluru                                               52000       9000
     11000         35      72000

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------

         1 Ashwin Vaishnav
New Delhi                                               50000       8000
     10000         30      68000

         4 Bhaavna Singh
Punjab                                                  48000       7500

    EMP_ID NAME
---------- --------------------------------------------------
CITY                                                       BP         DA
-------------------------------------------------- ---------- ----------
       HRA        AGE    NET_PAY
---------- ---------- ----------
      9500         32      65000

         2 Jane Smith
Los Angeles                                             45000       7000
      9000         28      61000

4. Experiment 4:

1. Write SQL queries to implement String functions.

SQL> select ascii('d') from dual;

ASCII('D')
----------
       100

SQL> select length('hello world') from dual;

LENGTH('HELLOWORLD')
--------------------
                  11

SQL> select concat('abc','def') from dual;

CONCAT
------
abcdef

SQL> select Replace('abcd1234', '123', 'zz') from dual;

REPLACE
-------
abcdzz4

SQL> select upper('abcdef') from dual;

UPPER(
------
ABCDEF

SQL> select lower('AbCDEf') from dual;

LOWER(
------
abcdef

SQL> select ltrim('           AbCDEf') from dual;

LTRIM(
------
AbCDEf


2. Write SQL queries to implement Date functions.


SQL> select current_date from dual;

CURRENT_D
---------
26-JUL-23

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
26-JUL-23 03.00.06.470000 PM +05:30

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2023

SQL> select last_day(date '2016-02-01') from dual;

LAST_DAY(
---------
29-FEB-16

SQL> select months_between(date '2016-08-01',date '2016-01-01') from dual;

MONTHS_BETWEEN(DATE'2016-08-01',DATE'2016-01-01')
-------------------------------------------------
                                                7
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
26-JUL-23 03.06.51.634000 PM +05:30


3. Create two tables and write SQL queries to implement SET operation


SQL> create table t1(s_no number(3),name varchar(10),age number(3),profession varchar(10));

Table created.

SQL> insert into t1 values(1,'Rahul',18,'student');

1 row created.

SQL> insert into t1 values(2,'Rohan',19,'student');

1 row created.

SQL> insert into t1 values(3,'Mike',28,'teacher');

1 row created.

SQL> select * from t1;

      S_NO NAME              AGE PROFESSION
---------- ---------- ---------- ----------
         1 Rahul              18 student
         2 Rohan              19 student
         3 Mike               28 teacher

SQL> drop table t2;

Table dropped.

SQL> create table t2(s_no number(3),name varchar(10),age number(3),profession varchar(10));

Table created.

SQL> insert into t2 values(1,'Rahul',18,'student');

1 row created.

SQL> insert into t2 values(2,'Sam',22,'teacher');

1 row created.

SQL> insert into t2 values(3,'Jack',30,'teacher');

1 row created.

SQL> select * from t2;

      S_NO NAME              AGE PROFESSION
---------- ---------- ---------- ----------
         1 Rahul              18 student
         2 Sam                22 teacher
         3 Jack               30 teacher


SQL> select * from t1 union select * from t2;

      S_NO NAME              AGE PROFESSION
---------- ---------- ---------- ----------
         1 Rahul              18 student
         2 Rohan              19 student
         2 Sam                22 teacher
         3 Jack               30 teacher
         3 Mike               28 teacher

SQL> select * from t1 union all select * from t2;

      S_NO NAME              AGE PROFESSION
---------- ---------- ---------- ----------
         1 Rahul              18 student
         2 Rohan              19 student
         3 Mike               28 teacher
         1 Rahul              18 student
         2 Sam                22 teacher
         3 Jack               30 teacher

6 rows selected.

SQL> select * from t1 intersect select * from t2;

      S_NO NAME              AGE PROFESSION
---------- ---------- ---------- ----------
         1 Rahul              18 student

SQL> select * from t1 minus select * from t2;

      S_NO NAME              AGE PROFESSION
---------- ---------- ---------- ----------
         2 Rohan              19 student
         3 Mike               28 teacher

SQL> select * from t2 minus select * from t1;

      S_NO NAME              AGE PROFESSION
---------- ---------- ---------- ----------
         2 Sam                22 teacher
         3 Jack               30 teacher

4. Write SQL queries to implement GROUPBY Clause


SQL> select count(name) from t1 where profession='student' group by age;

COUNT(NAME)
-----------
          1
          1


SQL> select name from t2 group by name;

NAME
----------
Jack
Rahul
Sam


SQL> select s_no,name from t2 group by rollup(s_no,name);

      S_NO NAME
---------- ----------
         1 Rahul
         1
         2 Sam
         2
         3 Jack
         3

5. Write SQL queries to combine GROUPBY Clause and ORDERBY Clause

SQL> select count(name) from t1 where profession='student' group by age order by age;

COUNT(NAME)
-----------
          1
          1