SQL

What is a database management system(DBMS)?

- A Database Management System (DBMS) controls the development,use, and maintenance of the databases used by the organization.

What is a foreign key?

- A reference to a primary key in a relational database table. The primary key in one table can be the foreign key in another. This can help avoid data redundancy and it reduces the risk of errors. - 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 relational database management system (RDBMS)? How is it different from (DBMS)

- A relational database is a type of database that organizes informations through the use of data tables, columns, and rows. A RDBMS is a type of DBMS but for relational databases.

What is a primary key?

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

What is CLAUSE in SQL?

SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records. For example - WHERE, HAVING clause.

What is the difference between SQL and MySQL?

SQL is the programming language while MySQL is a type of SQL Database

What is a unique constraint?

Specifically identifies which column should not contain duplicate values.

What is a view?

- A saved version of a customized form or list which defines the layout order and what fields appear on the form or list. - A view is a filtered list of records displaying only those records a user may need to see. Users and administrators may create new views for different scenarios - Advantages: Enforce business rules, consistency, Security, simplicity, space - Disadvantages: Performance issues, modifications

What are the Aggregate functions?

- AVG - calculates the average of a set of values - COUNT - counts rows in a specified table or view - MIN - gets the minimum value in a set of values - MAX - gets the maximum value in a set of values - SUM - calculates the sum of values

What is the difference between Clustered and Non-clustered index?

- Clustered : sort and store the data rows in the table or view based on key values. It defines the order of the data is physically stored in the table - Non-clustered : is an index structure separate from the data stored in a table that reorders one or more selected columns

What are constraints in SQL?

- Constraints are specific rules that are applied to data before it is entered into the table/database - This allows for better and more reliable data - NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, INDEX

What is a SQL injection attack? How do you protect yourself against these?

- SQL injection attacks are attacks that search for vulnerabilities and ultimately "inject" malicious code in your database. Develoeprs can prevent SQL injection vulnerabilities in web applications by utilizing parameterized database queries with bound, typed parameters and careful use of parameterized stored procedures in the database. - user inputs are converted to malicious sql code to get access to the entire database to prevent, don't use user inputs directly in queries. only accept parameterized variables which are populated w user inputs

What are tables and fields?

- Tables : a table is made up of fields and records that holds data - Records : Data is stored in records, a record is composed of fields and contains all of the data of a specific item in the table - Fields : Fields appear as columns in a database table, fields is a part of a record and contains a single piece of data for the subject of the record

What are the TRUNCATE, DELETE and DROP statements?

- Truncate removes all rows from the table - Delete command is sued to remove rows from a table. A WHERE clause can be used to only remove some rows - Drop command removes a table from the database

What is a database

-A database is a place where you can store and access information that is accessible to a computer system. - Organized collection of data

What is a query?

A command to retrieve or modify data from tables

What is an Alias in SQL?

Aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. (Select dbo.People as P)

What is Pattern Matching in SQL?

Allows you to search for patterns in data if you don't know the exact word or phrase you are seeking. This kind of SQL query uses wildcard characters to match a pattern, rather than specifying it exactly. (ex. You can use the wildcard "C%" to match any string beginning with a capital C)

What is normalization?

Converting poorly structured tables into two or more well-structured tables Each table should only have 1 "theme" - process of organizing data

What is a JOIN, describe different types?

INNER - Output data are only those that have a logical relationship between the two tables LEFT - Output data will be all of the first table mentioned while also showing only data that is connected from the second mentioned table RIGHT - Output data will be all of the second table mentioned while also showing only data that is connected from the first-mentioned table OUTER FULL - Output data is every data from both tables, connected or not connected

What is an Index?

Index are used to find rows with specific column values quickly.

What is a stored procedure?

Is a prepared SQL code that you can save, so the code can be reused over and over again.

What is a Recursive Stored Procedure?

Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps the programmers to use the same set of code n number of times.

What is SQL?

Structured Query Language (SQL) is a programming language used to create and use databases, tables, and relationships. SQL is divided into two categories: SQL statements for database definition and SQL statements for database processing (querying and updating). The database definition commands are referred to as a data definition language (DDL), and the database query and update commands are referred to as a data manipulation language (DML). SQL was developed by IBM, and is endorsed as a national standard by the American National Standards Institute (ANSI). Although a newer standard, SQL3, exists, the most widely implemented version of SQL is the ANSI SQL-92 standard. SQL is not a full-featured programming language, but rather it is considered to be a data sublanguage.

How can you select unique records from a table?

The SELECT DISTINCT statement is used to return only distinct (different) values

What are the various forms of Normalization?

There are 3 normal forms - First normal form = the information is stored in a relational table with each column containing atomic values. There are no repeating groups of columns - Second normal form = the table is in first normal form and all the columns depend on the table's primary key - Third normal form = the table is in second normal form and all of its columns are not transitively dependent on the primary key