36 lines
1.2 KiB
MySQL
36 lines
1.2 KiB
MySQL
|
USE tutorial;
|
||
|
|
|
||
|
|
SELECT positionId, forename, surname FROM Employee;
|
||
|
|
|
||
|
|
/* select only one of duplicates */
|
||
|
|
SELECT DISTINCT streetName FROM Employee;
|
||
|
|
|
||
|
|
/* add further conditions with where */
|
||
|
|
SELECT surname, EUR_GrossSalary FROM Employee
|
||
|
|
WHERE academicTitleId = 1
|
||
|
|
AND (EUR_GrossSalary BETWEEN 40000 AND 50000)
|
||
|
|
AND EUR_GrossSalary != 46000;
|
||
|
|
|
||
|
|
/* filter out non existing entries */
|
||
|
|
SELECT * FROM PostalLocation
|
||
|
|
WHERE cityDistrictName NOT LIKE ''
|
||
|
|
OR cityDistrictName IS NOT NULL;
|
||
|
|
|
||
|
|
/* check if something is a member of a list */
|
||
|
|
SELECT * FROM Employee WHERE academicTitleId IN (0, 1);
|
||
|
|
|
||
|
|
/* in with a sub query */
|
||
|
|
SELECT * FROM Employee WHERE academicTitleId IN (
|
||
|
|
SELECT academicTitleId FROM AcademicTitle WHERE academicTitleId > 1
|
||
|
|
);
|
||
|
|
|
||
|
|
/* check string for pattern */
|
||
|
|
/* %: zero or more characters */
|
||
|
|
/* _: one character */
|
||
|
|
SELECT surname FROM Employee WHERE surname LIKE 'A%';
|
||
|
|
|
||
|
|
/* where with regex */
|
||
|
|
SELECT surname FROM Employee WHERE REGEXP_LIKE(surname, '^[aB].*');
|
||
|
|
|
||
|
|
/* selects employees with have academic title of 1 or (exclusive) surname starts with B */
|
||
|
|
SELECT * FROM Employee WHERE academicTitleId = 1 XOR surname LIKE 'B';
|