SELECT TOP 5 * FROM Employee;
SELECT * FROM employee LIMIT 5;
MSSQL supported for with and without double quotation (“”) marks table creation.
CREATE TABLE example ("FirstName" VARCHAR(10), "LastName" VARCHAR(10), "Age" INT);
and
CREATE TABLE example (FirstName VARCHAR(10), LastName VARCHAR(10), Age INT);
can use
Only
CREATE TABLE example (FirstName VARCHAR(10), LastName VARCHAR(10), Age INT);
can use
Although multi-line comments used the same comment style, the single-line comment style is different.
Single-line comment
-- text_of_comment
multiple-line comment
/*
text_of_comment
*/
Single-line comment
# This comment continues to the end of line
multiple-line comment
/*
this is a
multiple-line comment
*/
MSSQL does not support Natural Join.
SELECT * FROM Album NATURAL JOIN Artist;
will not work
SELECT * FROM Album NATURAL JOIN Artist;
will work
MSSQL Server doesn't support USING clause.
SELECT* FROM Customer JOIN Employee USING (City, State);
will not work
Same results can be achieved using AND and JOIN clause in MSSQL.
SELECT* FROM Customer JOIN Employee
ON Customer.State = Employee.State AND Customer.City=Employee.City; will work on MySQL as well
SELECT* FROM Customer JOIN Employee USING (City, State);
Will work
MSSQL is supporting the FULL OUTER JOIN, but MySQL does not.
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Customer C FULL OUTER JOIN Employee E
ON E.EmployeeId = C.SupportRepId ;
Will work
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Customer C FULL OUTER JOIN Employee E
ON E.EmployeeId = C.SupportRepId ;
Will not work
SELECT Track.AlbumId,Track.Composer FROM Track JOIN InvoiceLine WHERE Track.TrackId = InvoiceLine.InvoiceLineId;
will not work
Same results can be achieved using ON and JOIN clause in MSSQL.
SELECT Track.AlbumId,Track.Composer FROM Track JOIN InvoiceLine
ON Track.TrackId = InvoiceLine.InvoiceLineId; will work on MySQL as well
SELECT Track.AlbumId,Track.Composer FROM Track JOIN InvoiceLine WHERE Track.TrackId = InvoiceLine.InvoiceLineId;
will work
SELECT * FROM track WHERE Name LIKE '%Love%'; Will return all rows that contain ‘Love’ word in the Name column.
SELECT Name FROM Track WHERE Name LIKE '%#%%' ESCAPE '#'; Search for ‘%’ in the Name column.
SELECT COUNT(Name) FROM track WHERE Name NOT LIKE 'Love%';
Company employees have been assigned as customer support representatives.
SELECT FirstName, LastName FROM Employee WHERE EXISTS
(SELECT SupportRepId FROM Customer WHERE Customer.SupportRepId=Employee.EmployeeId);
SELECT SUM(Invoice.Total) as Total_pay, Customer.CustomerId FROM Customer JOIN Invoice
ON Customer.CustomerId = Invoice.CustomerId GROUP BY Customer.CustomerId ORDER BY Total_pay DESC;
SELECT Customer.LastName, Customer.FirstName,Customer.Country, SUM(Invoice.Total) FROM Customer, Invoice
WHERE Customer.CustomerId =Invoice.CustomerId
GROUP BY Customer.LastName,Customer.FirstName, Customer.Country
HAVING Customer.Country = 'USA';
CROSS JOIN will return all the results that associate every row in the first table with every row in the second table.
SELECT album.Title, artist.Name, Track.Name FROM album, artist, Track;
The name Equi-join comes from the equality condition in the where clause.
SELECT Album.Title, Artist.Name FROM Album, Artist
WHERE Album.ArtistId = Artist.ArtistId;
MSSQL does not support Natural Join.
SELECT * FROM
Album NATURAL JOIN Artist;
MSSQL is not supporting the NATURAL JOIN, but MySQL does
Below query will produce the same results in both MSSQL and MySQL. (Alternative to the NATURAL JOIN).
SELECT * FROM
Album JOIN Artist
ON Album.ArtistId = Artist.ArtistId;
SELECT * FROM Track JOIN InvoiceLine
ON Track.TrackId= InvoiceLine.TrackId
WHERE Milliseconds BETWEEN 120000 AND 180000
AND Track.TrackId = InvoiceLine.TrackId;
In the Inner join, when join condition is satisfied a row is added to the result set. The Cross join, Equi-join, Natural join and condition join are example of Inner join.
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Employee E INNER JOIN Customer C
ON E.EmployeeId = C.SupportRepId;
There are three different kinds of outer join. The left outer join, the right outer join and the full outer join.
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Employee E LEFT OUTER JOIN Customer C
ON E.EmployeeId = C.SupportRepId;
The left outer join, accept all the results of the Inner join and it also accepts rows from the table on the left that do not satisfy the join condition.
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Customer C RIGHT OUTER JOIN Employee E
ON E.EmployeeId = C.SupportRepId ;
The right outer join, accept all the results of the Inner join and it also accepts rows from the table on the right that do not satisfy the join condition.
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Customer C FULL OUTER JOIN Employee E
ON E.EmployeeId = C.SupportRepId ;
The full outer join, accept all the results of the Inner join and it also accepts rows from both the table on the left and the table on the right that do not satisfy the join condition. MySQL does not support the full outer join. You can use UNION instead of FULL OUTER JOIN. (both MySQL and MSSQL support UNIO).
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Customer C LEFT OUTER JOIN Employee E
ON E.EmployeeId = C.SupportRepId
UNION
SELECT E.FirstName, E.LastName, C.FirstName, C.LastName FROM Customer C RIGHT OUTER JOIN Employee E
ON E.EmployeeId = C.SupportRepId ;
The self join is a join of one instance of a table to a second instance of the same table.
SELECT EA.FirstName, EA.LastName, EB.FirstName, EB.LastName FROM Employee EA, Employee EB
WHERE EA.ReportsTo = EB.EmployeeId;