PCS tech interview

what is join?

'join' used to connect two or more tables logically with or without common field(s)

what is a primary key?

A field (or group of fields) that uniquely identifies a given entity in a table

what is a candidate key?

A subset of attributes that can be used to uniquely identify any record in a table. Each table may have one or more candidate keys, which are chosen from column headings.

what is a transaction? what are acid properties?

ACID stands for atomicity, consistency, isolation, durability. database transactions are operated as a whole, meaning that if one part is executed, it must all be completed to the end. if it cannot be completed, then the entire operation must roll back to the very beginning

what are the advantages of DBMS over traditional file-based systems

DBMS systems were developed to handle the following difficulties of typical file-based systems: 1) data redundancy and inconsistency 2) difficulty in accessing data 3) data isolation 4) integrity problems 5) atomicity of updates 6) concurrent access by multiple users 7) security problems

what are the differences between DDL, DML, and DCL in SQL?

DDL - data definition language. this is what you use to CREATE, DROP, or ALTER tables. DML - data manipulation language. this is what you use to insert the values into the tables you just created with INSERT, SELECT, UPDATE. DCL - data control language. this authorizes access to the DB by using GRANT or REVOKE

what is database normalization?

Normalization is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancy and improve data integrity.

how do you print duplicate rows in a table?

SELECT name, section FROM tb1 GROUP BY name, section HAVING COUNT(*) >1

What is SQL?

Structured Query Language

what is a foreign key?

This is a column in one table that points to the primary key in another table.

What is a trigger?

a code associated with an INSERT, UPDATE, or DELETE

What are indexes?

a database index is a data structure that improves the speed of data retrieval operation on a database table they allow for faster search according to different frequently searched values

what is a stored procedure?

a function that contains a set of operations compiled together

What is a view in SQL? How to create one

a view is a virtual table based on the result-set of a SQL statement. it is created using: CREATE VIEW view_name AS ____ SELECT column_name(s) FROM table_name WHERE condition

what is identity?

also called AutoNumber is a column that automatically generates numeric values.

what are clustered and non-clustered indexes?

clustered indexes is the index according to which the data is physically stored on the disk. only one clustered index can be created on a given database non-clustered indexes don't define physical ordering of the data, but logical.

What is a super key?

it is a set of attributes that the schema is functionally dependent on. no two rows can have the same value of super key attributes

what is the difference between primary key and unique constraints?

primary keys cannot have a NULL value. unique constraints can have a NULL value. there can be only one primary key in a table but there can be multiple unique constraints.

what are views used for?

represents a subset of data contained in a table can hide the complexity of data they take very little space to store

what is the difference between trigger and stored procedure?

stored procedures can be called directly triggers cannot be called directly, they can only be associated with queries

what is the difference between the HAVING and WHERE clause?

when you write a SELECT statement with the WHERE clause, you are specifying a group. When you include the HAVING clause, this further specifies the group you just defined. Ex, select from "Animals" where all start with the letter A, having 2 or more letters. The biggest difference is that WHERE cannot contain aggregate functions.