Question

i) Write a command to create table student(RNO,name marks, dept) with proper datatypes and RNo as primary key ii) Write a command to create and drop sequence.

Answer 1

Of course. Here are the commands and explanations for creating a table and managing a sequence.

---

i) Command to Create a student Table

This command will create a table named student with the specified columns, appropriate data types, and RNO set as the primary key.

Syntax and Command

`sql
CREATE TABLE student (

RNO    NUMBER(5) PRIMARY KEY,
name   VARCHAR2(50) NOT NULL,
marks  NUMBER(5, 2),
dept   VARCHAR2(50)

);
`

Explanation of the Command

• CREATE TABLE student (...): This is the SQL statement that initiates the creation of a new table named student.
• RNO NUMBER(5) PRIMARY KEY:
  • RNO: This is the name of the first column (Roll Number).
  • NUMBER(5): This specifies the data type. NUMBER is used for numeric values. (5) indicates a precision, meaning it can store an integer up to 5 digits long (from 1 to 99999).
  • PRIMARY KEY: This is a constraint that uniquely identifies each record in the table. It ensures that every RNO is unique and cannot be empty (NULL).
• name VARCHAR2(50) NOT NULL:
  • name: The name of the column for the student's name.
  • VARCHAR2(50): The data type for variable-length character strings. (50) means it can store a name up to 50 characters long.
  • NOT NULL: This constraint ensures that every student record must have a name; it cannot be left blank.
• marks NUMBER(5, 2):
  • marks: The name of the column for marks.
  • NUMBER(5, 2): The data type for a decimal number. The precision is 5 (total digits) and the scale is 2 (digits after the decimal point). This allows for values like 100.00, 98.50, or 7.75.
• dept VARCHAR2(50):
  • dept: The name of the column for the department.
  • VARCHAR2(50): A variable-length string that can store a department name up to 50 characters long.

---

ii) Commands to Create and Drop a Sequence

A sequence is a database object that automatically generates a sequence of unique numbers. It is commonly used to create primary key values, ensuring they are always unique and sequential.

Command to Create a Sequence

This command creates a new sequence that can be used to generate roll numbers for the student table.

Syntax and Command

`sql
CREATE SEQUENCE student_rno_seq

START WITH 1
INCREMENT BY 1
CACHE 20;

`

Explanation of the Command

• CREATE SEQUENCE student_rno_seq: This is the statement that creates a new sequence named student_rno_seq. It's a good practice to name the sequence after the table and column it will be used for.
• START WITH 1: This specifies that the first number generated by the sequence will be 1.
• INCREMENT BY 1: This specifies that each subsequent number will be 1 greater than the previous one (1, 2, 3, ...).
• CACHE 20: This is a performance option. It pre-allocates a set of sequence numbers (in this case, 20) in memory for faster access.

Example of Using the Sequence

To use the sequence to insert a new student, you would use the .NEXTVAL pseudo-column:
`sql
INSERT INTO student (RNO, name, marks, dept)
VALUES (student_rno_seq.NEXTVAL, 'Alice', 95.5, 'Computer Science');
`
The first time this runs, student_rno_seq.NEXTVAL will be 1. The second time, it will be 2, and so on.

---

Command to Drop a Sequence

This command permanently deletes a sequence from the database.

Syntax and Command

`sql
DROP SEQUENCE student_rno_seq;
`

Explanation of the Command

• DROP SEQUENCE student_rno_seq: This statement finds the sequence named student_rno_seq and removes it from the database. This action is irreversible. Once dropped, the sequence cannot be recovered, and any code that refers to it will produce an error.