Basics of SQL praticles


SQL*Plus Session Log

```
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 31 20:00:44 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> conn system
Enter password:
Connected.

SQL> show user;
USER is "SYSTEM"

SQL> create user oraclebatch identified by mystudents;
User created.

SQL> select username from all_users;
USERNAME

https://unhealthyirreparable.com/cit2c8ca?key=7566cfdb82de49ba4912160b26b7621f
------------------------------
XS$NULL
ORACLEBATCH
APEX_040000
APEX_PUBLIC_USER
FLOWS_FILES
HR
MDSYS
ANONYMOUS
XDB
CTXSYS
APPQOSSYS
DBSNMP
ORACLE_OCM
DIP
OUTLN
SYSTEM
SYS

17 rows selected.

SQL> conn oraclebatch
Enter password:
ERROR: ORA-01017: invalid username/password; logon denied

SQL> conn oraclebatch
Enter password:
ERROR: ORA-01045: user ORACLEBATCH lacks CREATE SESSION privilege; logon denied

SQL> conn system
Enter password:
Connected.

SQL> grant resource,connect to oraclebatch;
Grant succeeded.

SQL> conn oraclebatch
Enter password:
Connected.

SQL> show user
USER is "ORACLEBATCH"

SQL> select * from tab;
no rows selected

SQL> create table Students(roll_no number(3), s_name varchar2(15), dob date);
Table created.

SQL> create table products
  2  (prodid number(2),
  3  product_name varchar2(20)
  4  );
Table created.

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PRODUCTS                       TABLE
STUDENTS                       TABLE

SQL> desc products
Name            Null?    Type
--------------- -------- ----------------
PRODID                  NUMBER(2)
PRODUCT_NAME            VARCHAR2(20)

SQL> describe students;
Name            Null?    Type
--------------- -------- ----------------
ROLL_NO                 NUMBER(3)
S_NAME                  VARCHAR2(15)
DOB                     DATE

SQL> alter table students add(per number(5,3));
Table altered.

SQL> alter table students add(mobile numbr(5,3));
ERROR at line 1: ORA-00907: missing right parenthesis

SQL> alter table students add(mobile varchar2(10));
Table altered.

SQL> alter table students modify(s_name varchar2(25));
Table altered.

SQL> alter table students modify(roll_no number(4),per number(6,3));
Table altered.

SQL> alter table students drop column per;
Table altered.

SQL> alter table students drop(dob,mobile);
Table altered.

SQL> alter table students add(per number(5,2), dob date);
Table altered.

SQL> alter table students set unused column per;
Table altered.

SQL> alter table students set unused(dob,s_name);
Table altered.

SQL> alter table students drop unused columns;
Table altered.

SQL> alter table students rename column roll_no to std_id;
Table altered.

SQL> alter table students rename to scholars;
Table altered.

SQL> rename scholars to students;
Table renamed.

SQL> truncate table students;
Table truncated.

SQL> insert into students values(101,79,'15-Jan-1996','Amit');
1 row created.

SQL> select * from students;
STD_ID   PER DOB       S_NAME
------- ---- --------- --------------------
101     79  15-JAN-96  Amit

SQL> insert into students values(102,71,'25-Feb-1992','Sumit');
1 row created.

SQL> insert into students values('103',73,'15-Jan-1996','Amit');
1 row created.

SQL> insert into students values('104A',73,'15-Jan-1996','Amit');
ERROR: ORA-01722: invalid number

SQL> insert into students values('104',87,'12-Dec-2001',25);
1 row created.

SQL> update students set s_name='Ravi' where std_id=104;
1 row updated.

SQL> insert into students(std_id,s_name)values(105,'Deepak');
1 row created.

SQL> insert into students(dob,per,s_name)values('11-Jun-1980',67,'Suresh');
1 row created.

SQL> insert into students values(107,65,null,'Dipika');
1 row created.

SQL> insert into students values(null,null,null,null);
1 row created.

SQL> select * from students;
STD_ID   PER DOB       S_NAME
------- ---- --------- --------------------
101     79  15-JAN-98  Avinash
102     89  21-FEB-01  Aniket
103     84  24-DEC-00  Anupam
104     76  25-OCT-96  Ravi
105     90             Nakul
        74  15-AUG-47  Mohan
107     65             Dipika
                       
8 rows selected.
```

### Key Operations Performed:
1. Created a new user `oraclebatch`
2. Granted necessary privileges
3. Created and modified tables (Students and Products)
4. Performed various ALTER TABLE operations (add/drop/modify columns)
5. Inserted, updated, and queried data
6. Handled errors during data insertion

The session demonstrates a complete workflow from user creation to table manipulation and data operations in Oracle Database.


# Quick Revision: Oracle SQL Commands

 1. User Management
 Create User
 CREATE USER oraclebatch IDENTIFIED BY mystudents;

Grant Privileges
GRANT CONNECT, RESOURCE TO oraclebatch;


Switch User
CONN username/password;
Show Current User
SHOW USER;


2. Table Operations
Create Table
CREATE TABLE Students (
    roll_no NUMBER(3),
    s_name VARCHAR2(15),
    dob DATE
);


Describe Table Structure
DESC students;
 or
DESCRIBE students;


List All Tables
SELECT * FROM TAB;


Alter Table (Add Column)
ALTER TABLE students ADD (per NUMBER(5,3));


Alter Table (Modify Column)
ALTER TABLE students MODIFY (s_name VARCHAR2(25));


Alter Table (Rename Column)
ALTER TABLE students RENAME COLUMN roll_no TO std_id;

Alter Table (Drop Column)
ALTER TABLE students DROP COLUMN per;
ALTER TABLE students DROP (dob, mobile);  -- Multiple columns

Mark Column as Unused (Logical Drop)
ALTER TABLE students SET UNUSED COLUMN per;
ALTER TABLE students SET UNUSED (dob, s_name);

Drop Unused Columns
ALTER TABLE students DROP UNUSED COLUMNS;

Rename Table
ALTER TABLE students RENAME TO scholars;
   or
RENAME students TO scholars;


Truncate Table (Delete all rows)
TRUNCATE TABLE students;


3. Data Manipulation (DML)
Insert Data
Full row insertion
INSERT INTO students VALUES (101, 79, '15-Jan-1996', 'Amit')
Partial insertion (specific columns)
INSERT INTO students (std_id, s_name) VALUES (105, 'Deepak');


Update Data
UPDATE students SET s_name = 'Ravi' WHERE std_id = 104;

Delete Data
DELETE FROM students WHERE std_id = 105;

Select Data
SELECT * FROM students;
SELECT std_id, s_name FROM students WHERE per > 75;


4. Quick Tips
- Use `DESC table_name` to view table structure.
- `TRUNCATE` is faster than `DELETE` (no rollback).
- `SET UNUSED` is safer than `DROP COLUMN` in production.
- Always `COMMIT` after DML operations to save changes.


Final Table Structure (Students)
STD_ID   PER    DOB         S_NAME
-------  -----  ----------  ---------
101      79     15-JAN-96   Amit
102      71     25-FEB-92   Sumit
103      73     15-JAN-96   Amit
104      87     12-DEC-01   Ravi
105      NULL   NULL        Deepak
NULL     67     11-JUN-80   Suresh
107      65     NULL        Dipika
NULL     NULL   NULL        NULL

Comments

Post a Comment

Popular posts from this blog

How to Upload Your Local Project to GitHub Properly

Image
How to Upload Your Local Project to GitHub – Step-by-Step Guide  📂 How I Learned the Right Way to Upload a Project to GitHub (From My College Mistake) 🏫 The College Phase: Uploading Projects to GitHub… the Wrong Way When I was in college, like many students, I worked on small development projects — web apps, Java-based tools, and mini full-stack apps. Back then, seniors and recruiters would often ask: “Do you have a GitHub link for your projects?” So I did what most beginners do: I created a new repository on GitHub. Then, I clicked Upload Files . I dragged and dropped my entire project folder . And hit “Commit”. Done, right? Well... not exactly . ❌ Why That Was the Wrong Way At first glance, it looked fine — my code was there on GitHub. But: There was no commit history Git wasn’t tracking actual changes over time No .gitignore file — so unnecessary files were also uploaded If I cloned it somewhere else, setup was messy Worse, I coul...
Read more

YouTube Videos, Translation, AI Tools Aur Hindi Mein Dekhne Ka Experience

  YouTube Videos, Translation, AI Tools Aur Hindi Mein Dekhne Ka Experience  Introduction Aaj kal har koi YouTube pe videos dekhta hai—tutorials, funny clips, interviews, ya podcasts. Lekin jab content English mein ho aur aapko Hindi mein samajhna ho, toh kya options hain? Language barrier content enjoy karne mein bada hurdle ho sakta hai, especially jab video English mein ho aur aapko Hindi mein samajhna ho ya Hindi audio chahiye. Is blog mein hum yehi samjhenge ki aap English YouTube video ko Hindi mein kaise dekh sakte hain ya sun sakte hain, kaun-kaun se AI tools available hain, aur kya limitations hain real life usage mein. YouTube Video Dekhne Mein Language Problem YouTube duniya ka sabse bada video platform hai—yahan har topic, har language, har audience ke liye kuch na kuch available hai. Lekin jab: Video English ya kisi aur language mein ho https://unhealthyirreparable.com/cit2c8ca?key=7566cfdb82de49ba4912160b26b7621f User ko Hindi mein content ch...
Read more

How to Build a Telegram Bot Using Node.js – Step-by-Step Guide

Image
How to Build a Telegram Bot Using Node.js – Step-by-Step Guide   Creating your own Telegram bot is easier than you think. In this guide, I’ll walk you through the complete process — from setting up your bot with BotFather , writing the bot using Node.js , to deploying it live 24/7 using Render . username: tns_server_error_bot 🧭 Step 1: Create a Bot on Telegram Using BotFather Open Telegram app In the search bar, type: BotFather Start the chat and type /start Then type /newbot and follow the steps: Choose a name for your bot (e.g., TNS Server Bot ) Choose a username that must end with _bot (e.g., tns_server_error_bot ) Copy the HTTP API token that BotFather gives you — we’ll use this later in our .env file. 💻 Step 2: Set Up Y our Project in VS Code (or Cursor) Open your preferred IDE (I used Cursor but you can use VS Code) Create a new folder for your project: mkdir tns_server_error_bot cd tns_server_error_bot Initiali...
Read more