SQL

SQL data types

https://www.w3schools.com/sql/sql_datatypes.asp

How to simulate FULL OUTER JOIN in MySQL

https://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

SQL Operators

https://www.w3schools.com/sql/sql_operators.asp

Normalization forms

1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi- valued key, remove it to a separate table. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

SELECT TOP

SQL Server / MS Access Syntax: SELECT TOP number/percent column_name(s) FROM table_name WHERE condition; Example: SELECT TOP 50 PERCENT * FROM Customers WHERE Country='Germany';

Stored Procedures for SQL Server

What is a Stored Procedure? A stored procedure is a prepared SQL code that you will save, so that the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed. Stored Procedure: CREATE PROCEDURE procedure_name AS sql_statement GO; Execute a stored procedure: EXEC procedure_name; Example: CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO; EXEC SelectAllCustomers;

Tuple

A single entry in a table is called a Tuple or Record or Row. A tuple in a table represents a set of related data.

Stored Procedure

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. e.g. sp_helpdb, sp_renamedb, sp_depends etc.

what are differences between a primary key and a unique key?

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Rules for Boyce-Codd Normal Form(BCNF)

Boyce-Codd Normal Form or BCNF is an extension to the third normal form, and is also known as 3.5 Normal Form. For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions: 1. It should be in the Third Normal Form. 2. And, for any dependency A → B, A should be a super key. How to satisfy BCNF? https://www.studytonight.com/dbms/boyce-codd-normal-form.php

COUNT DISTINCT and SELECT/GROUP BY return different number of rows

COUNT DISTINCT does NOT count NULL values, while SELECT/GROUP BY clauses roll up NULL values into one group. If you want to infer the number of distinct entries from the results of a query using joins and GROUP BY clauses, remember to include an "IS NOT NULL" clause to ensure you are not counting NULL values.

CREATE DATABASE & DROP DATABASE

Example: CREATE DATABASE databasename; DROP DATABASE databasename;

Rules for third normal form(3NF)

For a table to be in the third normal form, It should be in the Second Normal form. And it should not have Transitive Dependency. What is transitive dependency? How to resolve it? Transitive Dependency. When a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key. https://www.studytonight.com/dbms/third-normal-form.php

IF expressions

IF expressions are used to return one of two results based on whether inputs to the expressions meet the conditions you specify. They are frequently used in SELECT statements as a compact way to rename values in a column. The basic syntax is as follows: IF([your conditions],[value outputted if conditions are met],[value outputted if conditions are NOT met]) So we could write: SELECT created_at, IF(created_at<'2014-06-01','early_user','late_user') AS user_type FROM users

DAYOFWEEK()

MySQL DAYOFWEEK() returns the week day number (1 for Sunday,2 for Monday ...... 7 for Saturday ) for a date specified as an argument.

TIMESTAMPDIFF()

MySQL the TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another. It is not necessary that both the expressions are of the same type. One may be a date and another is datetime. A date value is treated as a datetime with a default time part '00:00:00'. The unit for the result is given by another argument. The unit should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

ISNULL()

MySQL/SQL Server IS NULL operator tests whether a value is NULL. If satisfied, then returns 1 otherwise returns 0. Difference between sum and count with is null function: SUM(ISNULL(COLUMN NAME)) returns the number of rows that are not null; COUNT(ISNULL(COLUMN NAME)) returns all rows in that column. Also, ISNULL() function lets you return an alternative value when an expression is NULL: SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products

SQL working with DATES

SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: a unique number

SQL SERVER Functions

SQL string functions, numeric functions, date functions, conversion functions, and advanced functions https://www.w3schools.com/sql/sql_ref_sqlserver.asp

Differences between a clustered and non-clustered index

A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Difference between Function (UDF) and Stored Procedure (SP)

SP-Must be executed explicitly with EXECUTE Statement whereas UDF-Must be called from SELECT Statement (Implicit calling) SP may or may not return any value whereas UDF must return something (scalar/table-valued) SP can have OUT parameters whereas UDF do not have any OUT parameters SP cannot return 2D Data (must always be scalar INT) with RETURN statement whereas UDF can return 2D data along with scalar data SP can CREATE temporary tables whereas UDF do not have any access to temporary tables SP can include any DDL and DML statements whereas UDF cannot have DDL and cannot do DML with permanent tables/views. UDF can do DML only with table variables created inside of it whereas SP can call UDF but UDF cannot call a SP

Why data analysis?

1. I have learned...in RPI using R. Supply chain analytics. Regression analysis, create charts, plots. Date analysis makes it easier to understand data, extract important info from massive database, combined with visualization tools like Tablaue more powerful. If a company wants to survive in a competitive market, good data analysis can be a competitive advantage. I haved used mysql and excel tools like functions and pivot tables in my last internship. Also I created ppt slides for the results and report to senior management. I found data analysis can be significant in decision making, business meetings and strategic planning for a company. That's when I decided to have a career in this field.

RDBMS

1. Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. 2. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. 3. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. 4. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

Advantages of using stored procedure

1. Stored procedure can reduced network traffic and latency, boosting application performance. 2. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. 3. Stored procedures help promote code reuse. 4. Stored procedures can encapsulate logic. You can change stored procedure codewithout affecting clients. 5. Stored procedures provide better security to your data.

FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. Example, CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (Persons) REFERENCES Persons(PersonID); To drop a foreign key: ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;

Primary key, Foreign key and Unique key constraints

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity. A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

Difference between a cross join and a full outer join

A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything. A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query's where clause, and in cases where the on condition can't be satisfied for those rows it puts null values in for the unpopulated fields.

Trigger

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures. Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

ACID components in database

ACID is a set of properties that you will need to apply when you try to modify the Database. ACID is an acronym and its meaning is as follow: Atomicity: In a transaction involving two or more discrete pieces of information, either all of the pieces are committed or none are. Consistency: A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started. Isolation: A transaction in process and not yet committed must remain isolated from any other transaction. Durability: Committed data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

Avoiding mismatches in using "GROUP BY"

As a way to prevent these logical mismatches or error messages, you will often hear a rule that "every non-aggregated field that is listed in the SELECT list must be listed in the GROUP BY list." For example, SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests FROM complete_tests GROUP BY Month ORDER BY Month ASC, test_name ASC; The syntax has mismatches in it. SELECT COUNT(DISTINCT dog_guid), breed_type, AVG(weight) AS avg_weight, FROM dogs GROUP BY breed_type; The syntax makes sense.

business intelligence

Business intelligence (BI) is the name given to the discipline and tools that enable the management of data for the purpose of analysis, exploration, reporting, mining, and visualization. Although aspects of BI appear in many applications, the BI approach and toolset provide a rich and robust environment to understand data and trends. SQL Server provides a great toolset to build BI applications, which explains Microsoft's continued gains in the growing BI market. SQL Server includes three services designed for BI: Integration Services (IS, sometimes called SSIS for SQL Server Integration Services), Reporting Services (RS), and Analysis Services (AS). Development for all three services can be done using the Business intelligence (BI) is the name given to the discipline and tools that enable the management of data for the purpose of analysis, exploration, reporting, mining, and visualization. SQL Server includes three services designed for BI: Integration Services (IS, sometimes called SSIS for SQL Server Integration Services), Reporting Services (SSRS), and Analysis Services (SSAS).

NOT NULL Constraint

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field. Example: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );

Candidate key & Super key & Alternate key

Candidate Key: The minimal set of attribute which can uniquely identify a tuple is known as candidate key. For Example, STUD_NO in STUDENT relation. The value of Candidate Key is unique and non-null for every tuple. There can be more than one candidate key in a relation. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT. The candidate key can be simple (having only one attribute) or composite as well. For Example, {STUD_NO, COURSE_NO} is a composite candidate key for relation STUDENT_COURSE. Note - In Sql Server a unique constraint that has a nullable column, allows the value 'null' in that column only once. That's why STUD_PHONE attribute as candidate here, but can not be 'null' values in primary key attribute. Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME) etc. Adding zero or more attributes to candidate key generates super key. A candidate key is a super key but vice versa is not true. Primary Key: There can be more than one candidate key in a relation out of which one can be chosen as primary key. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT but STUD_NO can be chosen as primary key (only one out of many candidate keys). Alternate Key: The candidate key other than primary key is called as alternate key. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for relation STUDENT but STUD_PHONE will be alternate key (only one out of many candidate keys).

CREATE Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement. CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... ); The following constraints are commonly used in SQL: NOT NULL - Ensures that a column cannot have a NULL value UNIQUE - Ensures that all values in a column are different PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table FOREIGN KEY - Uniquely identifies a row/record in another table CHECK - Ensures that all values in a column satisfies a specific condition DEFAULT - Sets a default value for a column when no value is specified INDEX - Used to create and retrieve data from the database very quickly

DCL- Data control language

DCL - Data Control Language - Grant, Revoke, Deny Grant - Give permissions to someone to perform an action on an object Grant Select On dbo.TableA To Jane Revoke - Take back a given permission from a user Revoke Select On dbo.TableA From Jane Deny - Prevent someone from doing an operation on an object Deny Select On dbo.TableA To Jane

DDL-Data definition language

DDL - Data Definition Language - Create, Alter, Drop Create - Create or define an object in SQL Server Create Table TableA (Column1 int, Column2 varchar(20), Column3 varchar(20)) Alter - Modify or change the table structure and columns Alter Table TableA Add Column4 nvarchar(50) Alter Table TableA Drop Column Column4 Alter Table TableA Alter Column Column3 varchar(200) Drop - Remove an object from the database Drop table TableA Drop database Adventureworks2012

DELETE FROM

DELETE FROM table_name WHERE condition; Example, DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste'; Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted! It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: DELETE FROM table_name; or DELETE * FROM table_name;

DML-Data manipulation language

DML - Data Manipulation Language - Insert, Update, Delete, Truncate* Insert - Add data to an existing table Insert into TableA values (1,'Hello','World') Update - Change the value in a row Update TableA Set Column3 = 'Univierse' Where Column3 = 'World' Delete - Remove rows of data from a table Delete From TableA Where Column1 = 1 Truncate - Remove ALL rows of data from a table (May be considered DDL by some) Truncate Table TableA

DQL-Data query language

DQL - Data Query Language - Select, From, Where, Group By, Having, Order By Select - Choose which columns of data to display Select Column1, Column2, Column3 From - Specify the table where data is being retrieved From dbo.TableA Where - Provide a condition to filter the result set Where Column1 = 1 Group By - Combine matching data points within a column Group By Column1 Having - Provide a condition to filter aggregated columns Having Sum(Column1) =< 100 Order By - Choose which columns to sort the data by Order By Column3 Desc

Database Normalization

Database normalization is a data design and organization processes applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Difference between DELETE and TRUNCATE commands

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. TRUNCATE: TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE cannot be rolled back. TRUNCATE is DDL Command. TRUNCATE Resets identity of the table. DELETE: DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement. DELETE Can be used with or without a WHERE clause DELETE Activates Triggers. DELETE can be rolled back. DELETE is DML Command. DELETE does not reset identity of the table. Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION

Entity Relationship Diagram

ERD is a blueprint to the overall design of the entire database. In this ER-Diagram, each table is known as an Entity. They are represented with the Rectangles The columns that would be found in those tables are displayed as attributes, with ovals This diagram is in a simple form, and displaying only the Entities and Attributes. There are no direct references to the type of relationships that are being used. Relationships are how entities relate or connect their data to one another. Relationships will often be displayed using a diamond shape, as seen in the next figure. With the descriptions, you see them in Diamond shapes, showing what action is being taken within the relationship. This just helps to understand the nature of the relation and how the data connects. In small annotations, you'll see [1:1], [1:M], and sometimes [M:N]. These are the instances or cardinality of how many times an entity is participating in the relationship. Most commonly you'll have either one- to-one, one-to-many, or many-to-many. 1 Employee → Assigned → 1 Parking Spot 1 Teacher → Teaches → Many Classes Many Students → Enroll In → Many Courses

EXISTS and NOT EXISTS

EXISTS and NOT EXISTS perform similar functions to IN and NOT IN, but EXISTS and NOT EXISTS can only be used in subqueries. The syntax for EXISTS and NOT EXISTS statements is a little different than that of IN statements because EXISTS is not preceded by a column name or any other expression. The most important difference between EXISTS/NOT EXISTS and IN/NOT IN statements, though, is that unlike IN/NOT IN statements, EXISTS/NOT EXISTS are logical statements. Rather than returning raw data, per se, EXISTS/NOT EXISTS statements return a value of TRUE or FALSE. As a practical consequence, EXISTS statements are often written using an asterisk after the SELECT clause rather than explicit column names. The asterisk is faster to write, and since the output is just going to be a logical true/false either way, it does not matter whether you use an asterisk or explicit column names. We can use EXISTS and a subquery to compare the users who are in the users table and dogs table, similar to what we practiced previously using joins. If we wanted to retrieve a list of all the users in the users table who were also in the dogs table, we could write: SELECT DISTINCT u.user_guid AS uUserID FROM users u WHERE EXISTS (SELECT d.user_guid FROM dogs d WHERE u.user_guid =d.user_guid); How could you determine the number of unique users in the users table who were NOT in the dogs table using a NOT EXISTS clause? SELECT COUNT(DISTINCT u.user_guid) FROM users u WHERE NOT EXISTS (SELECT d.user_guid FROM dogs d WHERE u.user_guid=d.user_guid);

Entity & Attribute & Relationship

Entity: An Entity can be any object, place, person or class. In ER Diagram, an entity is represented using rectangles. Describes the type of the information that is being mastered. An entity type typically corresponds to one or several related tables in database. Attribute: An Attribute describes a property or characterstic of an entity. For example, Name, Age, Address etc can be attributes of a Student. An attribute is represented using eclipse. Key Attribute: Key attribute represents the main characterstic of an Entity. It is used to represent a Primary key. Eclipse with the text underlined, represents Key Attribute. Relationship: A Relationship describes relation between entities. Relationship is represented using diamonds or rhombus(diamond shape).

Rules for second normal form(2NF)

For a table to be in the Second Normal Form, it must satisfy two conditions: The table should be in the First Normal Form. There should be no Partial Dependency. What is dependency and partial dependency? How to resolver the partial dependency? https://www.studytonight.com/dbms/second-normal-form.php

CASE expressions

For example, SELECT CASE WHEN cleaned_users.country="US" THEN "In US" WHEN cleaned_users.country="N/A" THEN "Not Applicable" ELSE "Outside US" END AS US_user, count(cleaned_users.user_guid) FROM (SELECT DISTINCT user_guid, country FROM users WHERE country IS NOT NULL) AS cleaned_users GROUP BY US_user OR it can be written as, SELECT CASE cleaned_users.country WHEN "US" THEN "In US" WHEN "N/A" THEN "Not Applicable" ELSE "Outside US" END AS US_user, count(cleaned_users.user_guid) FROM (SELECT DISTINCT user_guid, country FROM users WHERE country IS NOT NULL) AS cleaned_users GROUP BY US_user There are a couple of things to know about CASE expressions: Make sure to include the word END at the end of the expression CASE expressions do not require parentheses ELSE expressions are optional If an ELSE expression is omitted, NULL values will be outputted for all rows that do not meet any of the conditions stated explicitly in the expression CASE expressions can be used anywhere in a SQL statement, including in GROUP BY, HAVING, and ORDER BY clauses or the SELECT column list. CASE expressions often end up needing multiple AND and OR operators to accurately describe the logical conditions you want to impose on the groups in your queries. You must pay attention to the order in which these operators are included in your logical expressions, because unless parentheses are included, the NOT operator is always evaluated before an AND operator, and an AND operator is always evaluated before the OR operator. When parentheses are included, the expressions within the parenthese are evaluated first. That means this expression: CASE WHEN "condition 1" OR "condition 2" AND "condition 3"... will lead to different results than this expression: CASE WHEN "condition 3" AND "condition 1" OR "condition 2"... or this expression: CASE WHEN ("condition 1" OR "condition 2") AND "condition 3"...

Rules for first normal form(1NF)

If tables in a database are not even in the 1st Normal Form, it is considered as bad database design. Rule 1: Single Valued Attributes Each column of your table should be single valued which means they should not contain multiple values. We will explain this with help of an example later, let's see the other rules for now. Rule 2: Attribute Domain should not change This is more of a "Common Sense" rule. In each column the values stored must be of the same kind or type. For example: If you have a column dob to save date of births of a set of people, then you cannot or you must not save 'names' of some of them in that column along with 'date of birth' of others in that column. It should hold only 'date of birth' for all the records/rows. Rule 3: Unique name for Attributes/Columns This rule expects that each column in a table should have a unique name. This is to avoid confusion at the time of retrieving data or performing any other operation on the stored data. If one or more columns have same name, then the DBMS system will be left confused. Rule 4: Order doesn't matters This rule says that the order in which you store the data in your table doesn't matter.

How to run single statements or portion of SQL code?

If you select text in the Query Editor, then only the highlighted text is submitted to SQL Server when you press the Execute command button or the F5 key. This is an excellent way to test single SQL statements or portions of SQL code.

FIELD()

In MySQL we can sort the results in ascending or descending order very easily by using the ORDER BY clause. However, there are times when you want to sort the results in a specific order which cannot be done using the ASC or DSC. FIELD() of MySQL ORDER BY clause can be used to sort the results in a specific order. For example, SELECT name, species FROM `pet` ORDER BY FIELD(species, 'dog','cat','snake','bird'), name ASC

OLTP (Online Transaction Processing)

In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

subquery

In SQL Server, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. Example, SELECT p.product_id, p.product_name FROM products p WHERE p.product_id IN (SELECT inv.product_id FROM inventory inv WHERE inv.quantity > 10); SELECT suppliers.supplier_name, subquery1.total_amt FROM suppliers, (SELECT supplier_id, SUM(orders.amount) AS total_amt FROM orders GROUP BY supplier_id) subquery1 WHERE subquery1.supplier_id = suppliers.supplier_id; This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields. SELECT e1.last_name, e1.first_name, (SELECT MAX(salary) FROM employees e2 WHERE e1.employee_id = e2.employee_id) subquery2 FROM employees e1;

CREATE 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 column1, column2, ... FROM table_name WHERE condition; Example, CREATE VIEW [Current Product List] AS SELECT ProductID, ProductName FROM Products WHERE Discontinued = No; Then, we can query the view as follows: SELECT * FROM [Current Product List]; You can update a view using: CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; Drop a view: DROP VIEW view_Name; View Rules 1. Permanent Table Reference / NO TEMP 2. No Computed By 3. Order by allowed only w/Top clause 4. Into Options & Hints not allows 5. Schema - Three part naming 6. Schema - No Select *

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead. SELECT column_names FROM table_name WHERE column_name IS NULL; SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

UPDATE

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Example: UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1; Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

AUTO INCREMENT Field

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. Example, CREATE TABLE Persons ( ID int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "ID" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically): INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen');

Subqueries are used with some limits

One aspect of subqueries I didn't emphasize in the MySQL exercises is that you cannot use either LIMIT (MySQL only), TOP (Teradata only), or ORDER BY operations in subqueries positioned withn SELECT, WHERE, or HAVING clauses. The only situation in which you can use LIMIT, TOP, or ORDER BY operations in a subquery is when the subquery is used in a FROM clause that makes a derived table.

Over() with Ranking functions

Ranking functions can rank rows in a window based on a specified ordering. There are four ranking functions within SQL Server; ROW_NUMBER, RANK, DENSE_RANK and NTILE. An ORDER BY clause is mandatory, a PARTITION is not (if it is not specified the entire window is considered one group). SELECT SalesOrderID, SalesOrderNumber, CustomerID, ROW_NUMBER() OVER(ORDER BY CustomerID) AS RowNumber, RANK() OVER(ORDER BY CustomerID) AS [Rank], DENSE_RANK() OVER(ORDER BY CustomerID) AS DenseRank, NTILE(5000) OVER(ORDER BY CustomerID) AS NTile5000 FROM Sales.SalesOrderHeader ORDER BY CustomerID; ROW_NUMBER speaks for itself. It simply returns the number of the current row. RANK and DENSE_RANK assign a number to each row with a unique order value. That means that rows with the same order value (in this case CustomerID) get the same number. The difference between RANK and DENSE_RANK is that RANK assigns the current rank number plus the number of rows that have the same order value to the next row while DENSE_RANK always assigns the previous rank number plus one, no matter how many rows had the same order value. NTILE partitions, or tiles, the rows in groups of equal size. In this case the returned result had 31465 rows and we requested 5000 tiles of equal size. 31456 divided by 5000 equals 6 with a remainder of 1456. That means the NTILE value is increased after every six rows. Because there is a remainder of 1456 the first 1456 tiles get an additional row. In this case the ordering by CustomerID is not unique. As such the order within this window is not guaranteed by SQL Server and neither is your row number. That means that the record that is now row number two might be row number one or three the next time you run this query. If you want your row numbering to be guaranteed repeatable you should add another order by column which makes the ordering unique, for example SalesOrderID. This goes for all your windowing functions including aggregating functions, framing and offset functions.

Rankings

Rankings Ranking functions can rank rows in a window based on a specified ordering. There are four ranking functions within SQL Server; ROW_NUMBER, RANK, DENSE_RANK and NTILE. A rank must be followed by an OVER clause. That order clause must be followed by an ORDER BY clause, and may or may not have a PARTITION BY clause. Row_Number - 1,2,3,4,5 - It simply returns the number of the current row Rank - 1,1,3,3,5 - RANK assigns the current rank number plus the number of rows that have the same order value to the next row Dense_Rank - 1,1,2,2,3 - DENSE_RANK always assigns the previous rank number plus one, no matter how many rows had the same order value Ntile - Breaks up data in random order and ranking Select Row_Number() OVER (Partition By ID,Name Order By ID) as 'RwNo' From Employee SELECT SalesOrderID, SalesOrderNumber, CustomerID, ROW_NUMBER() OVER(ORDER BY CustomerID, SalesOrderID) AS RowNumber, RANK() OVER(ORDER BY CustomerID) AS [Rank], DENSE_RANK() OVER(ORDER BY CustomerID) AS DenseRank, NTILE(5000) OVER(ORDER BY CustomerID, SalesOrderID) AS NTile5000 FROM Sales.SalesOrderHeader ORDER BY CustomerID

Fourth normal form(4NF)

Rules for 4th Normal Form For a table to satisfy the Fourth Normal Form, it should satisfy the following two conditions: It should be in the Boyce-Codd Normal Form. And, the table should not have any Multi-valued Dependency. Let's try to understand what multi-valued dependency is in the next section. What is Multi-valued Dependency? A table is said to have multi-valued dependency, if the following conditions are true, For a dependency A → B, if for a single value of A, multiple value of B exists, then the table may have multi-valued dependency. Also, a table should have at-least 3 columns for it to have a multi-valued dependency. And, for a relation R(A,B,C), if there is a multi-valued dependency between, A and B, then B and C should be independent of each other. How to resolve multivalued-dependency? https://www.studytonight.com/dbms/fourth-normal-form.php

MIN() and MAX() Functions

SELECT MIN(column_name) FROM table_name WHERE condition; SELECT MAX(column_name) FROM table_name WHERE condition;

sql comments

Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). The following example uses a single-line comment as an explanation: --Select all: SELECT * FROM Customers; Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored. The following example uses a multi-line comment as an explanation: /*Select all the columns of all the records in the Customers table:*/ SELECT * FROM Customers;

Window function Over() with Aggregate functions

Sometimes you want to show the results of grouping data, like the number of orders, without actually grouping the data in your result. This is possible using window functions. A window function works like the GROUP BY clause we have seen earlier, except it calculates the result and returns a single value, making it possible to use these values in your query without grouping it. We can use the aggregate functions COUNT, AVG, MAX, MIN and SUM in a window and apply them to each row in your result set by using the OVER clause. SELECT SalesOrderID, SalesOrderNumber, COUNT(*) OVER() COUNT(SalesPersonID) AVG(SubTotal) OVER() MAX(SubTotal) OVER() MIN(SubTotal) OVER() SUM(SubTotal) OVER() OVER() AS NoOfOrders, AS OrdersWithSalesPerson, AS AvgSubTotal, AS MaxSubTotal, AS MinSubTotal, AS TotalSubTotal FROM Sales.SalesOrderHeader; As you can see we can apply aggregating functions without grouping SalesOrderID and SalesOrderNumber. Each row now simply shows the number of orders, the number of orders that have a sales person and the average, maximum, minimum and total subtotal. That is pretty useful, but often you want to show these results per group. For example you need to show the total subtotal for the customer in the row. This can be done using PARTITION BY in the OVER clause. The following query shows how to use this. SELECT SalesOrderID, SalesOrderNumber, CustomerID, SUM(SubTotal) OVER(PARTITION BY CustomerID) AS TotalSubTotalPerCustomer, SUM(SubTotal) OVER() AS TotalSubTotal FROM Sales.SalesOrderHeader; And you can specify multiple columns to partition by.

ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE-ADD COLUMN: ALTER TABLE table_name ADD column_name datatype; ALTER TABLE-DROP COLUMN: ALTER TABLE table_name DROP column_name; ALTER TABLE table_name ALTER COLUMN column_name datatype;

ANY AND ALL Operations

The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition. SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition); Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=). Example: SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);

BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. Example: SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) ); To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );

CREATE INDEX

The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up searches/queries. CREATE INDEX index_name ON table_name (column1, column2, ...); Example, If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas: CREATE INDEX idx_pname ON Persons(LastName, FirstName); DROP INDEX index_name;

CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database. CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... ); Example: CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); CREATE TABLE from another table: CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....;

DEFAULT Constraint

The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified. Example: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' ); ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'Sandnes'; ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;

DROP TABLE

The DROP TABLE statement is used to drop an existing table in a database. DROP TABLE table_name; The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself: TRUNCATE TABLE table_name;

FULL OUTER JOIN

The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records. Note: FULL OUTER JOIN can potentially return very large result-sets! Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well. Example: SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

Having

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); Example: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); or SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

INSERT INTO

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. INSERT INTO SELECT requires that data types in source and target tables match The existing records in the target table are unaffected INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. INSERT INTO table_name VALUES (value1, value2, value3, ...); Example, INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields. Example, CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) ); Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName). ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created). ALTER TABLE Persons DROP CONSTRAINT PK_Person;

SELECT INTO

The SELECT INTO statement copies data from one table into a new table. SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition; The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause. Example, the following SQL statement creates a backup copy of Customers: SELECT * INTO CustomersBackup2017 FROM Customers;

UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns; The columns must also have similar data types; The columns in each SELECT statement must also be in the same order; SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL: SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. Example: CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) ); To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD CONSTRAINT UC_Persons UNIQUE(ID); To drop a UNIQUE constraint for SQL Server, use the following SQL ALTER TABLE Persons DROP CONSTRAINT UC_Person;

Types of Relationships among entities

There are three types of relationship that exist between Entities. Binary Relationship: Two entities in relationship. Recursive(Unary) Relationship: When an Entity is related with itself. Only one entity in relationship. Ternary Relationship: Three entities in relationship . ER Diagram: Binary Relationship This is further divided into three types: I to 1 relationship: 1 Employee → Assigned → 1 Parking Spot 1 to many relationship: 1 Teacher → Teaches → Many Classes many to many relationships: Many Students → Enroll In → Many Courses ER Diagram: Recursive Relationship Example: Employees manage employees Employees are managed by employees ER Diagram: Ternary Relationship 见图片

LIKE Operator

There are two wildcards used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character LIKE Operator Description WHERE CustomerName LIKE 'a%' Finds any values that start with "a" WHERE CustomerName LIKE '%a' Finds any values that end with "a" WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position WHERE CustomerName LIKE 'a_%_%' Finds any values that start with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"

Difference between a HAVING CLAUSE and a WHERE CLAUSE

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

INNER JOIN, OUTER JOIN 书写形式

To use this more traditional syntax, you have to tell the database how to connect the tables using an ON clause that comes right after the FROM clause. Make sure to specify the word "JOIN" explicitly. This traditional version of the syntax frees up the WHERE clause for other things you might want to include in your query. Here's what one of our queries from the inner join lesson would look like using the traditional syntax: SELECT d.dog_guid AS DogID, d.user_guid AS UserID, AVG(r.rating) AS AvgRating, COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type FROM dogs d JOIN reviews r ON d.dog_guid=r.dog_guid AND d.user_guid=r.user_guid GROUP BY d.user_guid HAVING NumRatings > 9 ORDER BY AvgRating DESC LIMIT 200 You could also write "INNER JOIN" instead of "JOIN" but the default in MySQL is that JOIN will mean inner join, so including the word "INNER" is optional. If you need a WHERE clause in the query above, it would go after the ON clause and before the GROUP BY clause.

Types of SQL Statement

Update table Student Set Name='Tom' where StudentID= 111; DDL (Data Definition Language, contains Create, Alter, Drop) --- Used for Table creation. E.g. create table student (StudentID Int, Name Varchar (50), Class Varchar (20); DML (Data Manipulation Language, contains Insert, Update, Delete) --- Used for data management. E.g. Insert into student (StudentID, Name, Class) Values (1,'Alex', 'Graduation'); Delete from Student where StudentID = 111; DQL (Data Query Language, contain Select) --- Used data retrieval. E.g. Select Name from Student where StudentID =111; Select * from Student; DCL (Data Control Language, contains Grant, Revoke, Deny) --- Used for grating and revoking permissions to users.

Joins

Why joins? Tables are often divided into smaller pieces through the process of Normalization. This helps to limit data to only what is needed at any given time. The less data you pull, the faster the process. Sometimes though, we need more information and must combine tables together. This is often done using Joins. Types of joins and their differences: Joins - self join, cross join, Inner join, Right Outer join, Left Outer join, Full Outer join self join - A self JOIN is a regular join, but the table is joined with itself. SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City; Cross join - The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product. If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN. Inner Join - This will combine rows from multiple tables, where the column values must match. It will return the matching values from both tables. Select * From TableA Inner join TableB On TableA.Column1 = TableB.Column1 Left Outer Join - This will display the matching values between the two tables, just like inner join, but ALSO display the non-matching values from the first table Select * From TableA Left Outer join TableB On TableA.Column1 = TableB.Column1 Right Outer Join - This will display the matching values between the two tables, just like inner join, but ALSO display the non-matching values from the second table Select * From TableA Right Outer join TableB On TableA.Column1 = TableB.Column1 Full Outer Join - This join will display all values from both tables, whether they match or do not match Select * From TableA Full Outer join TableB On TableA.Column1 = TableB.Column

Syntax for aliases that omits the AS completely

You can also take advantage of aliases so that you don't have to write out the name of the tables each time. Here I will introduce another syntax for aliases that omits the AS completely. In this syntax, the alias is whatever word (or phrase, if you use quotation marks) follows immediately after the field or table name, separated by a space. Some people find it easier to read queries if you use the syntax without an AS to create table aliases, but retain the syntax with an AS to create field aliases (but both syntaxes work for field aliases and table aliases). So we could write: SELECT d.dog_guid AS DogID, d.user_guid AS UserID, AVG(r.rating) AS AvgRating, COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type FROM dogs d, reviews r GROUP BY DogID, UserID, d.breed, d.breed_group, d.breed_type HAVING NumRatings >= 10 ORDER BY AvgRating DESC LIMIT 200