Basic Flashcards
(92 cards)
1
Q
What is SQL?
A
Structured Query Language - programming language for managing data. Performs tasks such as retrieval, updation, insertion and deletion of data from a database.
2
Q
What is DBMS?
A
Data Base Management System - allows for the creation, maintenance, and use of a database. “File Manager “ that manages data
3
Q
What is RDBMS? What is NRDBMS?
A
Relational Data Base Management System - stores data in tables and can manipulate it. Examples : MySQL, SQL Server, Oracle
Non-relational database management system a database that does not use the tabular schema of rows and columns found in most traditional database systems; uses a storage model that is optimized for the specific requirements of the type of data being stored
4
Q
What is a database?
A
An organized collection and storage of data
5
Q
What are tables? What are fields?
A
Tables = data organized in columns and rows and the ROWS ( records), COLUMNS (fields)
6
Q
What is a primary key?
A
A unique identifier for each record. Ex: VIN, DL #, Phone # - NO DUPLICATE , NO NULL accepted
7
Q
What is a unique key?
A
A unique constraint that is a group of one or more than one fields or columns that identify a database or record
8
Q
What is a null value?
A
A data value that does not exist in database
9
Q
What is a foreign key?
A
A column or group of columns in a table that provides link between data in 2 tables - related to primary key of another table
10
Q
What is a join?
A
A clause/ keyword used to combine rows in 2 or more tables
11
Q
What are the types of join? Explain each
A
a. Inner Join =return matching rows in both tables
b. Right join =return all rows in right table - even if no matches in left table
c. Left join = return all rows in left table - even if no matches in right table
d. Full join = return rows when one table has match
e. Self join = joins table to itself
f. Cartesian join (Cross join) = returns products of sets of records of 2 or more joined tables
12
Q
What is normalization? What are the advanatages?
A
Database technique of reducing the data redundancy and dependency by organizing fields and tables in database - add, delete, modify fields that can be in 1 single table
Better Database organization More Tables with smaller rows Efficient data access Greater Flexibility for Queries Quickly find the information Easier to implement Security Allows easy modification Reduction of redundant and duplicate data More Compact Database Ensure Consistent data after modification
13
Q
What is denormalization?
A
Database technique of adding the data redundant data and dependency in database
14
Q
What are all the different normalizations?
A
a. First normal form (1NF) = cant have multiple values - should be atomic (cant be divided) values
b. Second normal form (2NF) =1NF + dependencies removed and placed in seperate table
c. Third normal form (3NF) = 2NF + should have no Transitive dependency ( an indirect relationship between values in same table)
d. “Boyce Codd Normal Form” =
e. Fourth normal form ( 4NF) = 3NF + should not have 2 or more independent Multivalued dependency (when 2 or more attributes in table are independent of each other but depend on 3rd attribute )
f. Fifth normal form ( 5NF) = 4NF + cant be broken down into smaller tables without data loss
g. Sixth normal form (6NF) = has not been determined yet!
15
Q
What is a view? What are views used for?
A
A virtual table with rows and columns used for data security ( the enclose table name) and analysis
- Restricting access to data.
- Making complex queries simple.
- Ensuring data independence.
- Providing different views of same data.
16
Q
What is an index?
A
Tuning method to allow faster retrieval of records from table
17
Q
What are the types of indexes?
A
a. Unique = Index key column doesn’t have duplicate values if column is unique
b. Clustered = Reorders the physical order of the table and search based on the key values; for “easy retrieval of data”; sorts out rows by column
c. Non-clustered = Does not alter the physical order of the table and maintains logical order of data; stored at one place and table data is stored in another place
18
Q
What is a cursor?
A
A control/ pointer that allows you to travel over rows and columns
19
Q
What is a relationship? What are the types?
What is an entity?
A
Established relationships between 2 or more tables - involves common fields in 2 or more tables
a. One - One (1:1) = 1 record in table associated with ONLY one record in another table
b. One - Many (1:N) = 1 record in table associated with one or more records in another table
c. Many - Many (M:N) = many records in table associated with many records in another table
A person, place, or thing in the real world about which data can be stored in a database
20
Q
What is a query?
A
A code written in order to get the information back from the database
21
Q
What is a subquery? What are the types of subquery?
A
A query inside another query - outer query (main query ) + inner query (sub-query) ; subquery first and then result passed to main query
a. Correlated =inner query dependent on values of outer query for its values
b. Non- correlated =inner query isnt dependent on values of outer query for its values; stands alone
22
Q
What is a stored procedure?
A
A function that consists of many SQL statements to access the database system.
23
Q
What is a trigger? What are nested triggers?
A
A code or programs that automatically execute with response to event on a table or view in a database
Execute after a DML or DDL operation is performed
24
Q
What is difference between TRUNCATE, DROP, and DELETE?
A
Truncate = delete data rows in tables but not table itself ; cant be rolled back
Delete = removes specific records from table
Drop = removes table from database, cant be rolled back
25
What are local and global variables and there differences?
Variables are the object which acts as a placeholder to a memory location
Local variables = variables which can be used or exist inside the function; not known and cant be used; can be created when function is called
Global variables =variables which can be used or exist throughout the program; cant be created when function is called
26
What is a constraint? What are the various levels?
Used to specify rules for data in a table by limiting the type of data that can go in data. Examples:
a. NOT NULL = enforces a column to NOT accept NULL values
b. CHECK = enables a condition to check the value being entered into a record
c. DEFAULT = provides a default value to a column when the INSERT INTO statement does not provide a specific value
d. UNIQUE
e. PRIMARY KEY
f. FOREIGN KEY
- column level constraint (Column-level constraints (except for check constraints) refer to only one column)
- table level constraint (Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition)
27
What is data integrity?
The accuracy and consistency of data stored in a database; Can define integrity constraints (are a set of rules used to maintain the quality of information)
28
What is auto increment?
Allows the user to create a unique number to be generated when a new record is inserted into the table; mainly used when primary key is introduced
29
What is a data warehouse?
A central repository of data from multiple sources of information; data are consolidated, transformed and made available for the mining and online processing
30
What is user defined functions? What are the different types?
Functions written by us the user of a program or environment for our own requirement ; can be called or executed when needed
a. Scalar function = accept one or more parameters but return only a single value result (mandatory) from an input value
b. Incline table valued function = similar to a "view" returns virtual table since it returns a table data type as a result; accepts parameters
c. Multi-statement table valued function = returns a table as output and this output table structure can be defined by the user
31
What is collation? What are the types of collation sensitivity?
Set of rules that determine how character data can be sorted, matched, arranged, and compared
a. Case Sensitivity – A and a and B and b. ( SQL is case insensitive)
b. Accent Sensitivity – (considers the accented and unaccented versions of letters to be identical for sorting purposes)
c. Kana Sensitivity – Japanese Kana characters. (Specifies that SQL Server distinguish between the two types of Japanese kana characters: Hiragana and Katakana. If not selected, SQL Server considers Hiragana and Katakana characters to be equal)
d. Width Sensitivity – Single byte character and double byte character ( distinguishes between a single-byte character and a double-byte character. If "WS" is not set/selected, SQL Server treats the single-byte and double-byte characters as the same for sorting purposes)
32
What are stored procedures? Advantages and Disadvantages of stored procedures?
What is a recursive stored procedure?
A prepared SQL code that you can save, so the code can be reused over and over again
Good = modular programming – means create once, store and call for several times whenever required
- fast execution
- reduce network traffic
- data security
Bad = can be executed only in the Database and utilizes more memory in the database server.
- hard to debug
- need expert developer - hard to code
- expensive
- hard to port (move) to upgraded versions
A procedure that references itself - "calls on itself"
33
What is online transaction processing?
A category of data processing focused on transaction-oriented tasks; Involves inserting, updating, and/or deleting small amounts of data in a database; mainly deals with large numbers of transactions by a large number of users IN REAL TIME.
34
What is a clause?
In-built functions available to us so we can deal with data easily stored in the table; help us filter and analyze data quickly; when we have large amounts of data stored in the database, we use Clauses to query and get data required by the user.
35
What are the most common important SQL commands?
```
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
```
36
What is a command? What is union, minus, alias, and interact commands?
Instructions used to communicate with a database to perform tasks, functions, and queries with data; can be used to search the database and to do other functions like creating tables, adding data to tables, modifying data, and dropping tables.
UNION = operator to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS = operator to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT= operator to return rows returned by both the queries.
ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.
37
What are aggregate and scalar functions? What are the different types
Aggregate functions - (performs a calculation on a set of values, and returns a single value)
These functions are used to do operations from the values of the column and a single value is returned.
AVG(): returns average value after calculating from values in a numeric column
COUNT(): count the number of rows returned in a SELECT statement:
FIRST(): returns the first value of the selected column
LAST(): returns the last value of the selected column
MAX(): returns the maximum value of the selected column
MIN(): returns the maximum value of the selected column
SUM(): returns the sum of all the values of the selected column
Scalar functions - (takes one or more parameters and returns a single value, helps simplify code)
These functions are based on user input, these too returns single value.
UCASE(): converts the value of a field to uppercase
LCASE(): converts the value of a field to lowercase
MID(): extracts texts from the text field
LEN(): returns the length of the value in a text field
ROUND(): round a numeric field to the number of decimals specified
NOW(): returns the current system date and time
FORMAT(): used to format how a field is to be displayed.
38
How can you create an empty table from an existing table?
Select * into studentcopy from student where 1=2
****Here, we are copying student table to another table with the same structure with no rows copied.
39
How to fetch common records from two tables?
Select studentID from student INTERSECT Select StudentID from Exam
40
How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
To display odd numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
41
How to select unique records from a table?
Select DISTINCT StudentID, StudentName from Student.
* use DISTINCT keyword
42
What is the command used to fetch first 5 characters of the string?
A. Select SUBSTRING(StudentName,1,5) as studentname from student
or....
B. Select LEFT(Studentname,5) as studentname from student
43
Which operator is used in query for pattern matching?
Write a SQL query to find the names of employees that begin with ‘A’?
LIKE operator is used for pattern matching, and it can be used as:
% - Matches zero or more characters.
_(Underscore) – Matching exactly one character.
Select * from Student where studentname like 'a%'
Select * from Student where studentname like 'ami_'
SELECT * FROM Table_name WHERE EmpName like 'A%'
44
What is a subset? What are the different subsets of SQL?
Function takes two sets of the same data type and returns a Boolean indicating whether the first set is a subset of the second set
a. Data Definition Language (DDL) – It allows you to perform various operations on the database such as CREATE, ALTER, and DELETE objects.
b. Data Manipulation Language(DML) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
c. Data Control Language(DCL) – It allows you to control access to the database. Example – Grant, Revoke access permissions.
45
What is the difference between CHAR and VARCHAR2 datatype in SQL?
Both Char and Varchar2 are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length
46
Write a SQL query to display the current date?
GetDate()
47
What is query optimization? What are the benefits?
Optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans - "lease estimated cost"
The advantages of query optimization are as follows:
- The output is provided faster
- A larger number of queries can be executed in less time
- Reduces time and space complexity
48
What is the ACID property in a database?
Atomicity: refers to a single logical operation of a data; either the entire transaction takes place at once or doesn’t happen at all
Consistency: data must meet all the validation rules; integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.
Isolation: concurrency control (controlling multiple computations happening at same time
Durability: once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs
49
What are the different operators available in SQL?
a. Arithmetic Operators: (add, multiply, subtract, modulus (divides left and right side and returns remainder) and division )
b. Logical Operators ( ALL, ANY, IN ,BETWEEN, AND, OR, NOT, EXISTS, LIKE, SOME)
c. Comparison Operators: (equal to, greater than, less than, less than or equal to, greater than or equal to, not equal to, not less than, not greater than)
50
What is the need for group functions in SQL?
Work on the set of rows and return one result per group. Some of the commonly used group functions are: AVG, COUNT, MAX, MIN, SUM, VARIANCE.
51
Write a SQL query to get the third-highest salary of an employee from employee_table?
```
SELECT TOP 1 salary
FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
```
52
How can you insert NULL values in a column while inserting the data?
a. Implicitly by omitting column from column list.
| b. Explicitly by specifying NULL keyword in the VALUES clause
53
What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?
BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.
Example of BETWEEN:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
Example of IN:
SELECT * FROM students where ROLL_NO IN (8,15,25);
54
Why are SQL functions used?
a. To perform some calculations on the data
b. To modify individual data items
c. To manipulate the output
d. To format dates and numbers
e. To convert the data types
55
What is the need for merge statement?
Allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
56
List the ways in which Dynamic SQL can be executed?
- Write a query with parameters.
- Using EXEC.
- Using sp_executesql.
57
What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
HAVING = used only with SELECT statement; used in a GROUP BY clause
WHERE = applied to each row before they are a part of the GROUP BY function in a query.
58
List some case manipulation functions in SQL?
There are three case manipulation functions in SQL, namely:
LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case.
Syntax: LOWER(‘string’)
UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase.
Syntax:UPPER(‘string’)
INITCAP: This function returns the string with the first letter in uppercase and rest of the letters in lowercase. Syntax: INITCAP(‘string’)
59
What is the main difference between SQL and PL/SQL?
SQL is a query language that allows you to issue a single query or execute a single insert/update/delete
PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple operations such as selects/inserts/updates/deletes.
60
What are the different authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode – SQL and Windows.
You can go to the below steps to change authentication mode in SQL Server:
1. Click Start> Programs> Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
2. Then select the server from the Tools menu.
Select SQL Server Configuration Properties, and choose the Security page.
61
What are STUFF and REPLACE function?
STUFF Function: This function is used to overwrite existing character or inserts a string into another string. Syntax: STUFF(string_expression,start, length, replacement_characters)
where,
string_expression:( it is the string that will have characters substituted)
start: (this refers to the starting position)
length: (this refers to the number of characters in the string which are substituted)
replacement_string: (they are the new characters which are injected in the string)
REPLACE function: This function is used to replace the existing characters of all the occurrences.
Syntax: REPLACE (string_expression, search_string, replacement_string)
Here every search_string in the string_expression will be replaced with the replacement_string.
62
What is COALESCE function?
Takes a set of inputs and returns 1st non-null value
63
What is ETL in SQL?
Extract (get data from sources), Transform ( make in tidy format), load (add data to tools to help us find insight)
64
How to create a table in SQL?
```
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
```
65
How to delete a table in SQL?
DROP TABLE table_name;
66
How to change a table name in SQL?
ALTER TABLE table_name
| RENAME TO new_table_name;
67
How to delete a row in SQL?
DELETE FROM table_name
| WHERE [condition];
68
How to create a database in SQL?
CREATE DATABASE database_name.
69
How to insert date in SQL?
“INSERT INTO tablename (col_name, col_date) VALUES (‘DATE: Manual Date’, ‘2020-9-10’)”;
70
How to see all tables in database?
show tables;
71
How to create, list, and use database?
create [ database name];
use [database name]
show databases;
72
How to extract all tables or view one table ?
SELECT *FROM Information_schema.tables;
| SELECT *FROM [table name]
73
Write a Query to display the number of employees working in each region?
SELECT region, COUNT(gender) FROM employee GROUP BY region;
74
Write SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal 10000.
By using BETWEEN in the where clause, we can retrieve the Employee Ids of employees with salary >= 20000and <=10000. SELECT FullName FROM EmployeeDetails WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary WHERE Salary BETWEEN 5000 AND 10000)
75
Given a table Employee having columns empName and empId, what will be the result of the SQL query below? select empName from Employee order by 2 asc;
“Order by 2” is valid when there are at least 2 columns used in SELECT statement. Here this query will throw error because only one column is used in the SELECT statement.
76
Can you get the list of employees with same salary?
Select distinct e.empid,e.empname,e.salary from employee e, employee e1 where e.salary =e1.salary and e.empid != e1.empid
77
What is the TOP clause? What is an alternative for TOP clause in SQL?
Used to specify the number of records to return
1. ROWCOUNT function
2. Set rowcount 3
3. Select * from employee order by empid desc Set rowcount 0
78
Will following statement give error or 0 as output? SELECT AVG (NULL)
Error. Operand data type NULL is invalid for Avg operator.
79
What is a schema? What are the 3 types of schema?
A logical collection of database objects
a. physical schema (how the data stored in blocks of storage)
b. logical schema (programmers and database administrators work at this level, at this level data can be described as certain types of data records gets stored in data structures, however the internal details such as implementation of data structure is hidden at this level (available at physical level)
c. view schema (end user interaction with database systems)
80
How to delete a column in SQL?
Example:
ALTER TABLE employees
DROP COLUMN age;
81
How to implement multiple conditions using WHERE clause?
SELECT * FROM employees WHERE first_name = ‘Steven’ AND salary <=10000;
82
What is SQL Injection?
a hacking technique which is widely used by black-hat hackers to steal data from your tables or databases. Let’s say, if you go to a website and give in your user information and password, the hacker would add some malicious code over there such that, he can get the user information and password directly from the database
83
How to find the nth highest salary in SQL?
This is how we can find the nth highest salary in MYSQL using LIMIT keyword:
SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1
84
How to find the nth highest salary in SQL?
SELECT *
INTO newtable
FROM oldtable
WHERE condition;
If we want to copy only some specific columns, we can do it this way:
SELECT column1, column2, column3, ...
INTO newtable
FROM oldtable
WHERE condition;
85
How to add a new column in SQL?
ALTER TABLE employees ADD COLUMN contact INT(10);
86
How to use LIKE in SQL?
SELECT * FROM employees WHERE first_name like ‘Steven’;
87
Write a Query to display odd records from student table?
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS RowID FROM student) WHERE row_id %2!=0
88
Define COMMIT and give examplle?
When a COMMIT is added in a transaction all chnages made in the transaction are written into the database permanently. Example: BEGIN TRANSACTION; DELETE FROM HR.JobCandidate WHERE JobCandidateID = 20; COMMIT TRANSACTION; The above example deletes a job candidate in a SQL server.
89
If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures?
Yes, SQL server drops all related objects, which exists inside a table like constraints, indexex, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table.
90
How to insert new records into a table?
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
91
SQL Order of Execution?
```
FROM
WHERE
GROUPBY
HAVING
SELECT
ORDERBY
LIMIT
```
92
How to rename a column?
Alter Table employees
| Rename Column first TO last
