My SQL set

Give the definition of a database schema

* container; * structure that represent the logical view of the entire database.

What is SQL injection?

* technique to manipulate a back-end database and retrieve information that was unauthorized

What is a Primary Key in SQL?

*A Primary Key is a column in a database where each row has a unique value; *Each table has only one Primary key; *No NULL value are allowed. For example: In a list of US citizens, the column with SSN would be a Primary Key whereas the first and last name columns combined with phone number would be a Unique Key

What is a Unique Key in SQL?

*A Unique Key is a column or group of columns that together hold unique values; *A table can have more than one Unique Key; for example: in a list of US citizens, the column with SSN would be a Primary Key, whereas the first and last name columns combined with phone number would be a Unique Key

ALTER TABLE (SQL Command)

*ALTER TABLE, lets you add columns to a table in a database ALTER TABLE table_name ADD column database;

AND (SQL Command)

*AND is an operator that combines two conditions. Both conditions must be TRUE for the row to be include in the result set. SELECT column-name(s) FROM table_name WHERE column_1 = value_1 AND column_2 value_2;

AS (SQL Command)

*AS is keyword in SQL that allows you to rename a column or table using an alias. SELECT column_name AS "Alias" FROM table_name;

AVG (SQL Command)

*AVG() is an aggregate function that returns the average value for a numeric column. SELECT AVG (column_name) FROM table_name;

BETWEEN (SQL Command)

*BETWEEN operator is used to filtering the result set within a certain range. The value can be numbers, text, or dates. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value_1 AND value_2;

What are the constraints in SQL?

*Constraints in SQL specify the rules for the data stored in the database: CHECK, DEFAULT, NOT NULL, INDEX, UNIQUE, PRIMARY KEY, UNIQUE KEY. *Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement.

Give the definition of a database schema

*Container; Skeleton structure that represents the logical view of the entire database

What are the different subsets of SQL?

*DDL(Data Definition Language) - it allows you to perform various operations on the database such as CREATE, ALTER, and DELETE objects. *DML(Data Manipulation Language) - It allows you to access and manipulate data, it help you to INSERT, UPDATE, DELETE and RETRIEVE date from the database. *DCL(Data Control Language) - It allows you to control access to the database.(GRAND or REVOKE access permissions)

DELETE (SQL Command)

*DELETE, statements are used to remove rows from a table. DELETE FROM table_name WHERE some_column = some_value;

What are the different types of SQL Statements?

*DQL-Data Query Language (only SELECT statement); *DML-Data Manipulation Language (INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE); *DDL-Data Definition Language (CREATE, AFTER, PROP, RENAME, TRANCATE, COMMENT); *TCL-Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION); *DCL-Data Control Language (GRANT, REVOKE)

What are Foreign keys?

*Foreign keys are used to allow one table to correlate its own data with the contents of some other table. *Foreign key field is a field than links one table to another tables primary or foreign key

LIKE (SQL Command)

*LIKE is a special operator used with the WHERE clause to search for specific pattern in a column. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

LIMIT (SQL Command)

*LIMIT is a clause that lets you specify the maximum number of rows the result set will have. SELECT column_name(s) FROM table_name LIMIT number;

MAX, MIN (SQL Command)

*MAX(), MIN() is a function that takes the name of a column as an argument and returns the largest value in that column, or smallest value in that column. for MAX command: SELECT MAX (column_name) FROM table_name; for MIN command: SELECT MIN (column_name) FROM table_name;

OR (SQL Command)

*OR is an operator that filters the result set to only include row where either condition is true SELECT column-name FROM table name WHERE column_name = value_1 OR column_name = value_2;

ORDER BY (SQL Command)

*ORDER BY is a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically. SELECT column_name FROM table_name ORDER BY column_name ASC | DESC;

ROUND (SQL Command)

*ROUND() is a function that takes a column name and an integer as an argument. It rounds the values in the column to the number of decimal places specified by the integer. SELECT ROUND (column_name, integer) FROM table_name;

SELECT DISTINCT (SQL Command)

*SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s) SELECT DISTINCT column_name FROM table_name;

SELECT (SQL Command)

*SELECT extracts data from a database; *SELECT statements are used to fetch data a database; *Every query will begin with SELECT SELECT column_name FROM table_name

What is SQL?

*SQL stand for Structured Query Language; *SQL statements are used to retrieve and update data in a database; *SQL work with database programs like MS Access, DB2, MS SQL server, Oracle, MySQL, ...etc. major keywords are: SELECT, UPDATE, DELETE, INSERT, WHERE, and others. Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard

Difference between SQL, MySQL and SQL Server.

*SQL stands fro Structure Query Language which contains the different SQL statements for querying against the DB tables in different RDBMS Software (Relational database management system). *MySQL and SQL Server are two different RDBMS software

SUM (SQL Command)

*SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column SELECT SUM(column_name) FROM table_name;

Difference between TRUNCATE, DELETE and DROP commands?

*The DELETE command is used to remove some or all ROWS from the table; *The TRUNCATE remove all rows from a table. The operation cannot be rolled back. *The DROP command removes a table from the database. All the table's rows, indexes, and privileges will also be removed.

UPDATE (SQL Command)

*UPDATE-updates data in a database; *UPDATE statements allow you to edit row in a table. UPDATE table_name SET some_column = some_value WHERE some_column = some_value;

WHERE (SQL command)

*WHERE-specifies condition that a record must satisfy before it is returned; *WHERE-is a clause that indicates you want to filter the result set to include only rows where the condition is true. SELECT column_name(s) FROM table_name WHERE column-name operator value;

Find a correspondence between comparisons and their designation in SQL 1. Equal; 2. Not Equal; 3. Greater than or Equal to; 4. Less than or Equal to; 5. Less than; 6. Greater than

1. Equal -->"=" ; 2. Not Equal -->"<>" ; 3. Greater than or Equal to -->">=" ; 4. Less than or Equal to -->"<=" ; 5. Less than -->"< " ; 6. Greater than -->">" ;

What port does SQL Server run on?

1433 is the standard port for SQL server

What is Source Control in SQL?

A component of Software configuration management, version control. Is the management of changes to documents, computer programs, large Websites, and other collections of information

Can a table Have more than one foreign key define?

A table can have any number of foreign keys defined. It can have only one primary key defined

What is difference between INNER JOIN and RIGHT OUTER JOIN?

An INNER JOIN only shows rows if there is a matching record on the other (right) side of the join. A (left) outer join shows rows for each record on the left-hand side, even if there are no matching rows on the other (right) side of the join.

SQL command - INNER JOIN

An INNER JOIN will combine rows from different tables, if the join condition is TRUE. (SELECT column_name(s) FROM table_1 JOIN table_2; ON table_1.column_name = table_2.column_name)

What is difference between INNER JOIN and LEFT OUTER JOIN?

An INNER JOIN will only select records where the joined keys are in both specified tables. A LEFT OUTER JOIN will select all records from the first table, and any records in the second table that match the joined key

SQL command - OUTER JOIN

An OUTER JOIN will combine rows from different tables even if the JOIN condition is not met. Every row in the LEFT table is returned in the result set, and if the JOIN condition is not met, the NULL values are used to fill in the columns from the RIGTH table (SELECT column_name(s) FROM table_1 LEFT JOIN table_2; ON table_1.column_name = table_2.column_name;)

Which of the following statements are DML (Data Manipulation Languages) commands? a. INSERT; b. UPDATE; c. GRANT; d. TRUNCATE; e. CREATE.

Answer: a. and b. The INSERT and UPDATE statements are Data Manipulation Language(DML) commands. note: *GRANT is Data Control Language(DCL) command *TRUNCATE and CREATE are Data Definition Language(DDL) commands

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 string of fixed length. Example: CHAR(10) can only store 10 characters and will not be able to store of any other length, wheres VARCHAR2(10) can store any length.

SQL command - COUNT

COUNT() is a function that takes the name of a table column as an argument and counts the number of row where column in not NULL. (SELECT COUNT(column_name) FROM table_name;)

SQL Commands

CREATE DELETE INSERT JOIN SELECT UPDATE

SQL command - CREATE TABLE

CREATE TABLE() creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table. (CREATE TABLE table_name(column_1 database, column_2 datatype)... and so on

Which operator is used to create a view programmatically?

CREATE VIEW

What is the correct syntax for the DELETE statement?

DELETE FROM <table_name> WHERE <PREDICATE>

Write the command to remove all employees names Alex from the Employee table

DELETE FROM Employee WHERE firstName = "Alex'

What is DML and DDL?

DML and DDL are subsets of SQL *DML stands for Data Manipulation Language, DML consist of INSERT, UPDATE and DELETE; *DDL stand for Data Definition Language, DDL consist of TRUNCATE, CREATE

What do you mean by data INTEGRITY?

Data Integrity defines the accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

What is FULL JOIN?

FULL JOIN returns all the records when there is a match n any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table

SQL command - GROUP BY

GROUP BY is a clause in SQL that is only used with an aggregate function. It is used in collaboration with the SELECT statement to orange identical data into groups (SELECT COUNT(*); FROM table_name; GROUP BY column_name)

List the different type of JOIN?

INNER JOIN; RIGHT JOIN; LEFT JOIN; FULL OUTER JOIN.

Which SQL command is used to ADD a row?

INSERT

What is the correct syntax for the INSERT statement?

INSERT INTO <Table Name> VALUE(value1, value2, etc.)

How do you ADD record to a table?

INSERT into table_name VALUES ('ALEX', 33 , 'M');

SQL command - INSERT

INSERT statements are used to add a new row to a table (INSERT INTO table_name (column_1, column_2, ... value_1, 'value_2', value_3 ...)

What is Primary Key in SQL?

In a database table, the Primary key is a column which has a unique value for each of the row within that column. It cannot have NULL value.

What is Unique Key?

In a database table, the Unique Key is a column which may or may not have null value of each of the row within that column.

What is INNER JOIN?

Inner Join in MySQL is the most common type of JOIN. It is used to return all the row from multiple tables where the JOIN condition is satisfied

What is the SQL CASE statement used for and give an example?

It allows you to embed an if-else like clause in the SELECT clause example: SELECT employee_name, case location WHEN 'Miami' then bonus *2 WHEN 'Boston' then bonus *,5 ELSE bonus END "New Bonus" FROM employee;

What is a JOIN command?

JOIN is a process of retrieve pieces of data from different sets(tables) and returns them to the user or program as one "joined" collection of data. *Getting information from related tables is called JOIN operation.

What is LEFT JOIN?

LEFT JOIN in MySQL is used to return all the rows from the left table, but only the matching rows from the right table where the join condition is fulfilled

What is MySQL?

MySQL is a freely available open-source Relational Database Management System(RDBMS) that uses SQL (Structured Query Language). SQL is the most popular language for adding, accessing, and managing content in a database. It is most noted for its quick processing, proven reliability, ease, and flexibility of use

What is a Primary Key?

Primary Key - the column (columns) that has completely unique data throughout the table is known as the Primary Key field

What is a RIGHT JOIN?

RIGHT JOIN in MySQL is used to return all the rows from the right table, but only the matching rows from the left table where the join condition is fulfilled.

How do you SELECT all records from the table?

SELECT * FROM table_name

Write an SQL Query to find the names of the employees starting with "A"?

SELECT name_table FROM Employees_table WHERE name like 'A%';

The SQL command "ORDER BY" can be used to order an amount in descending order?

SQL ORDER BY is used to sort the data in the ascending or descending order. But, It sorts the data in ascending order by default. To sort the data in descending order we use DESC keyword.

Describe SQL comments.

SQL comments are introduced by two consecutive hyphens (--) and ended by the end of the line.

What is SQL?

SQL-Structured Query Language, is a special-purpose programming language, designed for managing data held in a relational database management system

What does COMMIT do?

Saving all changes made by DML statements. note: DML-Data manipulation Language

What is SQL?

Structured Query Language, is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)

What is CREATE VIEW operator in SQL?

The CREATE VIEW command creates a view. A view is a virtual table based on the result set of an SQL statement.

What are the differences between Primary and Foreign key?

The Primary Key is the column or set of column used to uniquely identity the items in a table. A Foreign Key is used to uniquely identify the items in a different table, allowing join operation to happen.

How do you change value of the field?

The SQL UPDATE syntax (step#1: UPDATE employee_table SET number = 150 WHERE item_number = 'CD'(First Last name) step#2: UPDATE name_table SET status = 'enable' where phone = '7023551797'; step#3: UPDATE service_table SET request_data = table_date ('2021_02_04 9:29','yyyy-mm-dd hh 24:mm') where phone = '7023551797')

What is a table and field in SQL?

The Table is a collection of data elements organized in terms of rows and columns. A table is the simplest form of data storage. A Field is part of the record and contains a single piece of data for the subject of the record

What is types of SQL commands?

The basic categories of commands used in SQL to perform various functions. These functions include: Building database objects, Manipulation objects, Populating database tables with data, Updating existing data in tables, Deleting data, Performing database queries, Controlling database access, and Overall database administration. The main categories are: *DDL (DataDefinition Language) *DML (Data Manipulation Language) *DQL (Data Query Language) *DCL (Data Control Language) *Data administation commands *Transactional control commands

What is normalization?

The process of table design. is called normalization

List all the possible values that can be stored in a BOOLEAN data field

There are only two values that can be stored in a BOOLEAN data field: -1 (true) and 0 (false)

What is CARDINALITY?

Thinking mathematically, it is the number of elements in a set. Thinking in the database world, cardinality has to do with the counts in relationship, one-to-one, one-to-many, or many-to-many.

What is LEFT JOIN?

This JOIN will return all rows from the left table (this is the table you specify first in your code) and any matching records from the right table (this is the table you specify second in your code)

What is a RIGHT JOIN?

This JOIN will return all the rows from the right table (this is the table you specify second in your code) and any matches from the left table (this is the table you specify first in your code).

What is the correct syntax for the UPDATE statement?

UPDATE<table_name> SET<columnName1> = <new_value1>, <columnName2> = <new_value2>...etc. [WHERE<PREDICATE>]

SQL triggers. What is a trigger?

Well, it's a piece of SQL code that is activated when a certain event happens. A common usage is when a new record is added to a database, this triggers in invoked. for example, if a new employee joined the company (added to table Employees) email is sent to Manager, to IT Department (to prepare his office space), to Payroll Department...and so on.

Can one SELECT a random collection of rows from a table?

YES, using SAMPLE clause. Example: SELECT * FROM Employees SAMPLE(10); note: 10-mean 10% of rows selected randomly will be returned, or we can specify number of ROW (only for Oracle SQL) ROWNUM<5, on this case you will get 4 records returned.

Do you really need to write SQL as QA Engineer?

YES, you need to. No matter whether it is a small company or big, they have a database and you need to validate the data by writing SQL queries going into the database.

The SQL command "GROUP BY" can be used to order an amount in descending order?

Yes, GROUP BY in SQL is used to arrange similar data into group and Order By in SQL is is used to sort the data in the ascending or descending order.

Is the WHERE clause must appear always before the GROUP BY clause in SQL SELECT?

Yes, the proper order for SQL clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only SELECT and FROM clause are mandatory, rest ones is options.

How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?

You create a trigger that will automatically delete elements in the second table when elements from the first table are removed

How do you DELETE all records from a table but NOT the table itself?

by two different ways: *DELETE FROM <table_name> *TRUNCATE <table_name>

What is MySQL?

database server

Write an SQL query to display the current date?

in MySQL: SELECT curdate(); or SELECT current_date(); in Oracle: SELECT current_date FROM Dual;

Write a SQL SELECT query that only returns each city only once from the Students table? Do you need to order this list with an ORDER BY clause?

we need to use the DISTINCT command, to avoid duplicates, and definitely, we use ORDER BY for sort alphabetically. SELECT DISTINCT City_table FROM Students_table;