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';