Code SQL

Question 1

Create tablespace

Answer 1

CREATE TABLESPACE TBS1
DATAFILE ‘C’
SIZE n m
AUTOEXTEND ON
MAXSIZE UNLIMITED;

Question 2

Delete Tablespace

Answer 2

DROP TABLESPACE TBS1INCLUDING CONTENTS AND DATAFILES;

Question 3

Change default tablespace

Answer 3

ALTER USER HR DEFAULT TABLESPACE TBS1;

Question 4

Create table in tablespace

Answer 4

CREATE TABLE HR.TAB1(id NUMBER(30));

Question 5

Insert multiple entries into Table with 1 Column

Answer 5

INSERT INTO HR.TAB1
SELECT level FROM dual
CONNECT BY level <= 100000;

Question 6

Create table with 2 columns

Answer 6

CREATE TABLE HR.TAB2(id NUMBER(30), name VARCHAR2(50));

Question 7

Insert multiple entries into table with 2 columns

Answer 7

INSERT INTO HR.TAB2(id, name)
SELECT level, ‘Name’ ||level – || for string concat
FROM dual
CONNECT BZ level <= 100;

Question 8

Create table with constrains

Answer 8

CREATE TABLE HR.TAB1
(user_id NUMBER (30) CONSTRAINT PK_USER_ID PRIMARY KEY,
user_name VARCHAR(300 CHAR) CONSTRAINT NN_USER_NAME NOT NULL,
create_date DATE CONSTRAINT NN_CREATE_DATE NOT NULL,
CONSTRAINT fk_student_id FOREIGN KEY (sid) REFERENCES UNI.student (sid));

Question 9

User Anlegen

Answer 9

CREATE USER UNI IDENTIFIED BY uni;

Question 10

GRANTS

Answer 10

GRANT CREATE SEESION TO UNI;
GRANT CREATE TABLE TO UNI;
GRANT SELECT ON HR.HR_TAB4 TO UNI;

Question 11

Insert Value into Table

Answer 11

INSERT INTO table(sid, name, create_date)
VALUES(1,’durra’,SYSTIMESTAMP,TO_DATE(‘2023-06-30));

Question 12

Package Header

Answer 12

– Create a package header
CREATE OR REPLACE PACKAGE circle_area_pkg IS
– Package Specification
FUNCTION calculate_area(radius NUMBER) RETURN NUMBER;
END circle_area_pkg;

Question 13

Create Package Body

Answer 13

CREATE OR REPLACE PACKAGE BODY circle_area_pkg IS
– Package Body
FUNCTION calculate_area(radius NUMBER) RETURN NUMBER IS
pi CONSTANT NUMBER := 3.14159;
BEGIN
RETURN pi * radius * radius;
END calculate_area;
END circle_area_pkg;

Question 14

Call Function in Package

Answer 14

– Call the calculate_area function from the package
DECLARE
radius NUMBER := 5;
area NUMBER;
BEGIN
area := circle_area_pkg.calculate_area(radius);
DBMS_OUTPUT.PUT_LINE(‘Area: ‘ || area);
END;

Question 15

Create and edit Sequence

Answer 15

– sequence stuff
CREATE SEQUENCE my_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

ALTER SEQUENCE my_seq
INCREMENT BY 2;

SELECT my_seq.NEXTVAL FROM DUAL;
– enable dbms output
SET SERVEROUTPUT ON;

– PL/SQL Block
DECLARE – Declare Variables, allocate memory for cursors and define data types
currval_seq NUMBER;
BEGIN – Execute SQL Code and/or Statements -> After Begin there can be implemented an exception block for exceptions
SELECT my_seq.CURRVAL – can just be accessed after calling NEXTVAL at least once!
INTO currval_seq
FROM DUAL;

DBMS_OUTPUT.PUT_LINE(currval_seq);
DBMS_OUTPUT.PUT_LINE(my_seq.NEXTVAL); END;

Question 16

Insert Value into Table

Answer 16

INSERT INTO table(sid, name, create_date)
VALUES(1,’durra’,SYSTIMESTAMP,TO_DATE(‘2023-06-30));

Question 17

Insert Value into Table

Answer 17

INSERT INTO table(sid, name, create_date)
VALUES(1,’durra’,SYSTIMESTAMP,TO_DATE(‘2023-06-30));

Question 18

Merge Table

Answer 18

MERGE INTO member_staging x – target table
USING (SELECT member_id, first_name, last_name, rank FROM members) m – source tables/data
ON (x.member_id = m.member_id) – merge condition
WHEN MATCHED THEN – necessary to define
UPDATE SET x.first_name = m.first_name,
x.last_name = m.last_name,
x.rank = m.rank
WHERE x.first_name <> m.first_name OR – <> = !=
x.last_name <> m.last_name OR
x.rank <> m.rank
WHEN NOT MATCHED THEN – necessary to define
INSERT(x.member_id, x.first_name, x.last_name, x.rank)
VALUES(m.member_id, m.first_name, m.last_name, m.rank);