Oracle SQL

What is the first normal form?

(1NF) Create a PK- Identify each set of related data with a primary key. Singular- Eliminate repeating attributes in individual tables (e.g. "customer1", "customer2", "customer3", and so on). Atomic/Granular- Divide up each attribute until it can't be divided anymore (e.g. "name" attribute becomes "first name", "middle name", and "last name").

What is the second normal form?

(2NF) Needs to be in 1NF. Every non-key attribute of the table is dependent on the primary key as a whole. No partial dependencies; meaning, if there is a composite key then each non-key attribute must rely on the ENTIRE composite key, not just SOME of it.

What is the third normal form?

(3NF) Needs to be in 2NF. No transitive dependencies. Every non-key attribute is not dependent upon any non-key attribute. The key. The whole key. And nothing but the key, so help me Codd.

What are the different constraints in Oracle SQL?

-NOT NULL: cannot store a null value -UNIQUE: each row in a column must have a unique value -PRIMARY KEY: A combination of NOT NULL and UNIQUE. Ensures that a column has a unique identity which helps to find a particular record in a table more easily and quickly. -FOREIGN KEY: Ensures the referential integrity of the data in one table matches values in another table. -CHECK: Ensures that the value in a column meets a specific condition. -DEFAULT: Specifies a default value for a column. NOT in Oracle SQL.

What are many-to-many relationships?

A Many-to-Many (M:N) relationship is defined as a relationship between two tables where many rows from one table can have multiple matching rows in another table. Neither table can support a foreign key to relate the tables, so a junction table (aka join table or associative entity) is created. A junction table is a database table that contains foreign key references to two or more other database tables. It is the standard way of creating a many-to-many relationship between tables. In example, students to courses; a course has many students and a student has many courses.

What is a nested query (subquery)?

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict a search condition.

What is a transaction?

A change made to the database. Essentially every time you see a commit after 1 or more SQL statements.

What is a primary key?

A column or set of columns that uniquely identify each row of a table. A driver's license number, a VIN number, or UPC (universal product code) are examples of primary keys. A primary key must be unique and not null.

What is a cross join? (cartesian join)

A cross join does not have a WHERE or ON clause, therefore it produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

What is a cascade delete?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. Cascade delete can be setup during table creation or you can ALTER the table later.

What is the difference between a global table and a local table in SQL?

A local temp table is visible to the creator that is connected only, disbands after disconnect. A global temp table is saved in the database and visible by all users, even after the initial crating user disconnects.

What is a foreign key?

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

What is an orphan record?

A record whose foreign key references a deleted/nonexistent record. In Oracle SQL, we can't have orphan records because it enforces referential integrity.

What is a Sequence?

A sequence is an object in PL/SQL that generates a numeric value based on some defined rules.

What is a database?

A structured set of data, held in a computer. Databases support storage and manipulation of data. Databases make data management significantly easier.

What is a self join?

A table being joined on itself. A table of people that has a foreign key to itself; the foreign key representing each person's spouse.

What is the difference between INNER JOIN and OUTER JOIN?

An INNER JOIN will return only the rows that actually match based on the join predicate. An OUTER JOIN will also return rows that are NOT common between both tables.

What is an index?

An index can be created in a table to find data more quickly and efficiently. Indexes allow the database application to find data fast; without reading the whole table. The users cannot see the indexes, they are just used to speed up searches/queries. Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns that will be frequently searched against.

What are the essential properties of a transaction?

Atomicity: the transaction must execute completely or not at all. Consistency: refers to the integrity of the underlying data store. Isolated: refers to transaction execution without interference from other processes or transactions. Durability: the data is not lost during system crashes.

What does the BETWEEN operator do?

BETWEEN creates a range condition. SELECT * FROM table WHERE column1 BETWEEN 5 and 7

What are procedures? What are functions?

Both are a program unit/module that performs a particular task- a block of code. Functions: return a single value; mainly used to compute and return a value. Procedures: do not return a value directly; mainly used to perform an action.

What are some special types of primary keys?

Composite Key: a primary key made up of two or more attributes. Natural Key: A unique identifying characteristic that has meaning outside of the database (SSN). Surrogate Key: A primary key that ONLY has meaning within the database. Auto generated key.

Important interfaces in the JDBC API

Connection, Statement, PreparedStatement, CallableStatement, ResultSet DriverManager (class not interface)

What are constraints in SQL?

Constraints are rules that restrict values of a column/attribute in a table. Constraints are created during the creation of tables.

Which sub-language needs to be committed to create a transaction?

DML

What are the sub languages of SQL?

Data Manipulation Language (DML): INSERT, SELECT, UPDATE, DELETE Data Definition Language (DDL): CREATE, ALTER, TRUNCATE, DROP Data Query Language (DQL): SELECT Data Control Language (DCL): GRANT, REVOKE Transaction Control Language (TCL): COMMIT, SAVEPOINT, ROLLBACK

What is a table?

Data is contained by objects called "tables" or "entities". Each table has a name, rows, and columns. Columns (attributes) define what datatype is being stored and describe what the data represents. Rows (records) represent each set of data that has been entered into the database.

What is an ERD?

Entity Relationship Diagram A data modeling technique that illustrates the relationships between tables. Entities: represent people, places, items, events, or concepts. Attributes: represent properties or descriptive qualities of an entity. (AKA data elements) Relationships: represent the link between different entitites.

What is an explicit cursor?

Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. CURSOR cursor_name IS select_statement; Working with an explicit cursor involves four steps: Declaring the cursor for initializing in the memory Opening the cursor for allocating memory Fetching the cursor for retrieving data Closing the cursor to release allocated memory

Difference between a procedure and a function?

Functions only use DQL statements, only have IN parameters, have a return type, cannot call procedures, and themselves can be called using an execution block OR a select statement. Procedures can use DML staements, have IN or OUT parameters, don't have a return type, can call other procedures or functions, and themselves can only be called using an execution block.

What is the difference between WHERE and HAVING?

HAVING is used in conjunction with aggregate functions. It can be used similar to WHERE but it can also be used to specify a search condition for a group of records. (grouped by the GROUP BY keyword after an aggregate function).

What does the IN operator do?

IN creates a set of values in which the condition will be true. SELECT * FROM table WHERE person_name IN ('Enriko', 'Feliz', 'Anthony')

How would you use IN and EXIST? What is the difference?

IN works best for a small finite set of data, whereas EXISTS is better for subqueries. In most cases, EXISTS will be much more efficient (and faster) than an IN statement. When using an IN combined with a subquery, the database must process the entire subquery first, then process the overall query as a whole, matching up based on the relationship specified for the IN. With an EXISTS or a JOIN, the database will return true/false while checking the relationship specified. Unless the table in the subquery is very small, EXISTS or JOIN will perform much better than IN.

What is the difference between LEFT JOIN, RIGHT JOIN, and FULL JOIN?

INNER JOIN: Returns all rows when there is at least one match in BOTH tables. LEFT JOIN: Return all rows from the left table and the matched rows from the right table. RIGHT JOIN: Return all rows from the right table and the matched rows from the left table. FULL JOIN: Return all rows when there is a match in ONE of the tables. You can also create a DISTINCT left, DISTINCT right, and DISTINCT full joins; which means the result sets won't include matched rows that appear in both tables. SELECT * FROM artist a LEFT OUTER JOIN album b ON a.artistid = b.artistid;

How do you add a record to a tabe?

INSERT INSERT INTO TABLE VALUES( _____, _____, _____, _____, _____ ); INSERT INTO TABLE(col1, col3) VALUES ( _____, _____ );

What is an implicit cursor?

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

What is a view?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW view_name AS SELECT column_name(s) FROM table WHERE condition

What are some types of joins?

Inner, Full Outer, Left Outer, Right Outer, Self, Cartesian/Cross, Equi, Non-Equi, Natural, Theta, etc

What does the AS keyword do?

It creates an alias for an attribute.

What does the ORDER BY operator do?

It sorts attributes. It can sort in either ascending or descending order and can be given multiple attributes. SELECT * FROM table ORDER BY column2 ASC, column5 DESC

Difference between JDBC and ODBC

JDBC provides a connection between front end java applications to backend databases. ODBC provides the same services, except for other applications other than java.

What is JDBC?

Java Database Connectivity. It is an API that allows us to easily communicate with the database from Java code.

Can you have more than one primary key in one table?

No. A table can have one and only one primary key constraint. However, a primary key can contain more than one column. A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.

What are one-to-one relationships?

One-to-One (1:1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only a SINGLE matching row. This relationship can be created using Primary key-Unique foreign key constraints; again, the foreign key needs to be set to unique. For example, a person can have only one passport.

What is a cursor?

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc. A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: Implicit cursors and Explicit cursors

How do you prevent SQL injection?

Prepared Statements (parameterized queries) Stored Procedures Escaping all user supplied input

What is PL/SQL?

Procedural Language SQL, and it is Oracle's additions on top of basic SQL. It adds functionality that allows SQL to behave like a programming languages. There are functions, for loops, if statements, etc.

What is a record?

Records (entries) are rows of a table. They represent a set of data that has been entered into a table, and therefore has a value for each attribute in the table (even null). Each record requires a primary key.

What does REPLACE do?

Replace a subset in a column with a replacement, for all elements that apply. SELECT REPLACE( COLUMN_A, 'existing', 'replaced') from TABLENAME.

What does the DISTINCT keyword do?

Returns the unique records from a query - no duplicates. SELECT DISTINCT * FROM table

How would you select the top 10% of rows from a table in SQL?

SELECT TOP 10% * FROM TABLENAME;

What is a SQL join?

SQL JOIN allows us to "lookup" records on other tables based on the given conditions between two tables. For example , if we have a person foreign key within a car table, we may want to join the two tables to obtain information on each car's owner.

What is an aggregate function?

SQL aggregate functions return a single value, calculated from values in a column. •AVG() - Returns the average value •COUNT() - Returns the number of rows •FIRST() - Returns the first value •LAST() - Returns the last value •MAX() - Returns the largest value •MIN() - Returns the smallest value •SUM() - Returns the sum

What is a scalar function?

SQL scalar functions return a single value, based on the input value. -upper() - Converts a field to upper case -lower() - Converts a field to lower case -MID() - Extract characters from a text field -length() - Returns the length of a text field -ROUND() - Rounds a numeric field to the number of decimals specified -NOW() - Returns the current system date and time -FORMAT() - Formats how a field is to be displayed

What types of exceptions does JDBC produce?

SQLExceptions

What are some of the String manipulation methods in SQL?

SUBSTRING, TRIM, LTRIM, RTRIM, REVERSE, REPLACE, LENGTH, CONCAT, UCASE, LCASE, etc.

What is a set operator?

Set Operators combined records from multiple result sets into a single result set. Result sets may be combined only if the count and order of column datatypes are the same. UNION operator returns only distinct rows that appear in either result. UNION ALL operator returns all rows. does not eliminate duplicate selected rows. INTERSECT operator returns only those rows returned by both result sets. MINUS operator returns only unique rows returned by the first query but not by the second.

What is SQL?

Structured Query Language. SQL is the language used to communicate with a RDBMS. We used Oracle SQL (PL/SQL).

How do you add a column to the table?

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ALTER TABLE table_name ADD column_name datatype

What is referential integrity?

The foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

What is normaliztation?

The process of organizing data in a database. We use normalization to eliminate redundancy and inconsistent dependencies. There are six normal forms, though any organizations only use the first three.

What is a database schema?

The schema is the collection of database objects (tables, triggers, etc), their constraints, and the relationships defining how each object interacts with one another.

What is a database management system (DBMS)?

The set of rules and software that allows a database to be accessed and manipulated. We used relational database management systems (RDBMS).

Tell me about yourself.

This is a simple guide on how to approach this: - Full Name - College/Degree - Position - Technologies used - Projects you worked on and implemented these technologies - Any other relevant experience

What is a trigger?

Triggers are stored programs in PL/SQL, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events: A database manipulation (DML) statement (DELETE, INSERT, or UPDATE). A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). Triggers could be defined on the table, view, schema, or database with which the event is associated.

What things do we need to make a connection to a database?

URL (endpoint + port + DB name) Username Password Driver (ojdbc.jar)

How can you retrieve rows from a database table?

Use a SELECT statement SELECT [ column_list OR * ] [FROM table] [JOIN table ON join_predicate] [WHERE condition] [GROUP BY {col_name} [ASC or DESC] [HAVING condition] [ORDER BY {col_name} [ASC or DESC]

How would you combine two columns into one column?

Use the CONCAT command. Select CONCAT(A, B) as "Combination" from TABLENAME

How do we check if an attribute has a null value?

Use the IS NULL or IS NOT NULL operators

What is GRANT and REVOKE?

You can grant users various privileges to tables and revoke those privileges. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL. GRANT privileges ON object TO user; REVOKE privileges ON object FROM user;

What is the difference between DELETE, TRUNCATE, and DROP?

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster than DELETE. The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

What does the LIKE operator do?

The LIKE operator is provided a string, it then looks for any record that follows a specific pattern given by the provided string. E.g. a provided string "My%" will find any record that begins with "My". % is a wildcard symbol that represents zero or more characters. _ is a wildcard symbol that represents exactly one character. Each wildcard may be used more than once and in various combinations.

What are many-to-one relationships?

The Many-to-One (M:1) relationship is simply the 1:M relationship but from the perspective of the other table. For example, passengers to vehicles.

What is the difference between ORDER BY and GROUP BY?

The ORDER BY keyword is used to sort the result-set by one or more columns. The GROUP BY statement is used in conjunction with the aggregate functions to group attributes with the same value; the aggregate function will then be performed on each group of records.

What are one-to-many relationships?

The One-to-Many (1:M) relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship; the foreign key doesn't have to be unique in this case. For example, vehicles can have multiple passengers, but a passenger cannot be in multiple vehicles.

What is the purpose of SELECT INTO?

The SELECT INTO statement selects data from one table and inserts it into a new table. SELECT * INTO new_table FROM table1

What is the WHERE clause? What is the purpose of AND and OR?

The SQL WHERE clause is used to specify a condition while fetching the data from table(s). You would use WHERE clause to filter the records and fetching only necessary records. The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc. The SQL AND and OR operators are used to combine multiple conditions to create more specific search criteria. These two operators are called conjunctive operators. SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...OR [conditionN];

What is data?

information