DP_16_1_PRACTICE.sql

-- .Command that automatically generates sequential numbers- CREATE SEQUENCE (CREATE SEQUENCE some_name)
-- ·Generates a numeric value- Sequences
-- ·Returns the next available sequence value- NEXTVAL
-- ·Specifies the interval between sequence numbers- INCREMENT BY (INCREMENT BY n)
-- ·Specifies a maximum value of 10^27 for an ascending sequence and -1 for a descending sequence (default)- NOMAXVALUE
-- ·returns the current sequence value- CURRVAL
-- ·specifies the minimum sequence value- MINVALUE (MINVALUE n)
-- ·specifies whether the sequence continues to generate values after reaching its maximum or minimum values- CYCLE/NOCYCLE
-- ·specifies a minimum value of 1 for an ascending sequence and – (10^26) for a descending sequence (default)- NOMINVALUE
-- ·specifies a maximum or default value the sequence can generate- MAXVALUE/NOMAXVALUE (MAXVALUE n | NOMAXVALUE)
-- ·specifies the first sequence number to be generated- START WITH (START WITH n)
-- ·specifies how many values the Server pre-allocates and keeps in memory- CACHE/NOCACHE (CACHE n | NOCACHE)


-- 1.  Using CREATE TABLE AS subquery syntax, create a seq_d_songs table of all the columns in the DJs on Demand database table d_songs.
-- Use the SELECT * in the subquery to make sure that you have copied all of the columns.
CREATE TABLE seq_d_songs
AS ( SELECT * FROM d_songs);
DESCRIBE seq_d_songs;
DESCRIBE d_songs;
SELECT * FROM d_songs;
SELECT * FROM seq_d_songs;

-- 2.  Because you are using copies of the original tables, the only constraints that were carried over were the NOT NULL
-- constraints. Create a sequence to be used with the primary-key column of the seq_d_songs table.
-- To avoid assigning primary-key numbers to these tables that already exist,
-- the sequence should start at 100 and have a maximum value of 1000. Have your sequence increment by 2
-- and have NOCACHE and NOCYCLE. Name the sequence seq_d_songs_seq.
CREATE SEQUENCE seq_d_songs_seq
INCREMENT BY 2
START WITH 100
MAXVALUE 1000 NOCYCLE NOCACHE;


-- 3.  Query the USER_SEQUENCES data dictionary to verify the seq_d_songs_seq SEQUENCE settings.
SELECT * FROM user_sequences WHERE sequence_name = UPPER('seq_d_songs_seq');

-- 4.  Insert two rows into the seq_d_songs table. Be sure to use the sequence that you created for the ID column.
-- Add the two songs shown in the graphic.
INSERT INTO seq_d_songs (id,title,duration,artist,type_code)
VALUES(seq_d_songs_seq.NEXTVAL,'Surfing Summer',NULL,NULL,12);
INSERT INTO seq_d_songs (id,title,duration,artist,type_code)
VALUES(seq_d_songs_seq.NEXTVAL,'Victory Victory','5 min',NULL,12);
SELECT * FROM seq_d_songs ORDER BY id DESC;
SELECT * FROM user_sequences WHERE sequence_name = UPPER('seq_d_songs_seq');




-- 5.  Write out the syntax for seq_d_songs_seq to view the current value for the sequence. Use the DUAL table.
-- (Oracle Application Developer will not run this query.)
SELECT seq_d_songs_seq.CURRVAL FROM DUAL;


-- CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL.
-- 6.  What are three benefits of using SEQUENCEs?
-- ·May be used to generate identity column values - the unique numbers. They also avoid concurrency issues.
-- ·Save time and efforts of coding.
-- ·Same sequence may be used in multiple tables since they are generated independent of the table using it, but I will prefer to use one sequence for one purpose.
-- ·There is cache option available in sequences.
-- ·These are sharable objects and multiple users can access it.

-- 7.  What are the advantages of caching sequence values?
-- Using cache option gives a slight performance advantage as the numbers are pre-allocated and stored in-memory.

-- 8.  Name three reasons why gaps may occur in a sequence?

-- ·Rolling back a statement containing a sequence.
-- ·System crash if system caches values in-memory
-- ·Same sequence being used by multiple tables.

-- Extras:
-- 1.  Create a table called “students”. You can decide which columns belong in that table and what datatypes these columns require.
-- (The students may create a table with different columns; however, the important piece that must be there is the student_id column with a numeric datatype. This column length must allow the sequence to fit, e.g. a column length of 4 with a sequence that starts with 1 and goes to 10000000 will not work after student #9999 is entered.)
CREATE TABLE students(
    student_id NUMBER(6) CONSTRAINT sdt_sdt_id_pk PRIMARY KEY,
    fname VARCHAR2(12),
    lname VARCHAR(20),
    sex CHAR(1),
    major VARCHAR2(24)
);

-- 2.  Create a sequence called student_id_seq so that you can assign unique student_id numbers for all students that you add to your table.

CREATE SEQUENCE student_id_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 999999 NOCYCLE NOCACHE;

SELECT * FROM user_sequences WHERE sequence_name = UPPER('student_id_seq');

-- 3.  Now write the code to add students to your STUDENTS table, using your sequence “database object.”
INSERT INTO students(student_id ,fname,lname ,sex ,major )
VALUES(student_id_seq.NEXTVAL,'Hemant','Kumar','M','Web');
INSERT INTO students(student_id ,fname,lname ,sex ,major )
VALUES(student_id_seq.NEXTVAL,'Hemant2','Kumar2','M','CyberSec');
SELECT * FROM user_sequences WHERE sequence_name = UPPER('student_id_seq');

SELECT * FROM students;