Skip to content

Latest commit

 

History

History
256 lines (183 loc) · 9.58 KB

README.md

File metadata and controls

256 lines (183 loc) · 9.58 KB
Raw

FCIM UTM

▒▐█▀▄░▒▐█▀█▄
▒▐█▀▀▄▒▐█▌▐█
▒▐█▄▄▀▒▐█▄█▀

Technical University of Moldova

FCIM | UTM | Spring 2022



p. Galcenco Boris


Chisinau 2022



c.AMS | FCIM UTM Spring 2022 | 104 | 21.09.2021

c. BD | Galcenco | 104 | 09.09.2022

-- Intro Database –

  1. What is database purpose? – Store and manage data.

  2. When did the Database concepts born? - Thousands of year ago, the Egyptians first ( store and preserve date, curve it into stone).

  3. How the data gets generated? - We generate manual data, or automatically.

  4. Who consumes the data? - Applications (software) or/and people.

  5. What for is data being consumed? - Apps/Users

  6. Databases are used to.... - apps ( efficiently store data and return it when requested)

  7. What is DBMS? - Database Management Systems

  8. What is the SQL? - Structured Query Language

  9. What are the data use cases (1.. 2.. 3.. ) - directly and indirectly, 1) writing and executing queries against data in database, 2)executing searches, or submit/save button, generating a sequence statement to retrieve that date, 3)business/organizations

  10. Which "hats" you wear when working with data? - backend dev, data analysis, scientist, engineer, data researcher

  11. Since when the SQL is around here? (19..) – 1974

  12. In what order do records appear when executing a SQL query without ORDER BY clause? - no particular order, in order they were added to database

  13. What is the default order for ORDER BY clause? - ASC

  14. Can fields which were not mentioned in the SELECT list be used in the ORDER BY clause? - Yes

  15. Can fields which were not mentioned in the SELECT list be used in the ORDER BY clause? - Yes

  16. as in example, the "top" instruction shows only x mentioned enteties, the "with ties" allow to show more if some of them have the same data

  17. Can WHERE clause be applied after ORDER BY clause? No

  18. Will "ID BETWEEN 10 AND 100" statement return records with ID equal to 10 or 100? Yes

  19. Is filtering by text data case sensitive? E.g. WHERE Name = 'Ion' Case insensitive

  20. List the wildcards you've learned and explain their functionality % Represents zero or more characters _ Represents a single character [] Represents any single character within the brackets ^ Represents any character not in the brackets

  • Represents any single character within the specified range
  1. What is the default masks for Date formats in MS SQL? (choose 2 out of 4) 'MM/DD/YYYY'



Laboratory work #3

(Functions in queries)

https://www.youtube.com/watch?v=Fm8od9L9HMg


  • Each and every function in MS SQL requires at least 1 parameter

False

  • What will be the correct version of get current date function call?

SELECT GETDATE()

  • Can a result of one function be used as a parameter of another function?

Yes

(Text calculations)

https://www.youtube.com/watch?v=HJKraiIoYPU


  • What is (are) the correct syntax of concatenate function in MS SQL?

+ and CONCAT()

  • Do CAST() and CONVERT() functions behave similarly in MS SQL?

Yes No

  • You have a table:
   SELECT ActorName
   FROM (
        SELECT 'Tom BigBoy Cruise' as ActorName
        UNION
        SELECT 'Dwayne Rock Johnson' as ActorName
   ) A;

     Using substring() function instead of right() and left(), write a SQL query which will show the First Name and Last Name only in single field. As an answer please provide a complete SQL query (without any additional text)

     So in our case, expected output is:

         Tom Cruise
         Dwayne Johnson

-


(Date Calculations)

https://www.youtube.com/watch?v=Q2xhAafpRJo


  • Try it yourself in DB and answer: what will happen if you will wrongly specify less number of chars than required by date mask?

     For example:
convert(char(8), SomeDateField, 103)

     When 103 date style requires 10 symbols?

2008-06-01 00:00:00.000 --- > 01/06/20

  • Try it yourself in DB and answer: what will happen if you will specify more number of chars than required by date mask?

     For example: convert(char(10), SomeDateField, 3)

When 3 date style requires 8 symbols? * Write a query to select a month from the RegistryDate field * Considering that we have the following field with a single row:

select CAST('2024-09-26 00:00:00.000' AS DATETIME) as FutureModifiedDate

  • What will be the output of the following function call?

DATEDIFF(YY, FutureModifiedDate, GETDATE()) * 2 -2

Laboratory work #4

(Group By and Having)

https://www.youtube.com/watch?v=oWkvHodS9cA


Which function does not belong to the same group as others? * MIN() COUNT() ROUND() AVG() Look at the query below. Is it a valid one?

SELECT GroupName, AVG(Mark), COUNT(*) FROM Students GROUP BY GroupName; * Yes No Look at the query below. Is it a valid one?

SELECT GroupName, AVG(Mark), COUNT(*) FROM Students ORDER BY GroupName GROUP BY GroupName; * Yes No What is the difference between GROUP BY and GROUP BY WITH ROLLUP? * How behaves GROUP BY WIH ROLLUP if you are grouping by two or more fields? * What is the difference between WHERE and HAVING clauses? * Using Address and CustomerAddress tables from AdventureWorksLT2019 database, write a query that:

Calculates a count of records under each separate [Address.CountryRegion] and [CustomerAddress.AddressType] fields combination. The query should not return row if the count is greater than 100. It should also take only the records with non-NULL values in [Address.AddressLine2] field.

Please provide the whole query as the answer * Captionless Image



Laboratory work #4

Table HumanResources.Employee from AdventureWorks2019 database contains a field called LoginID. It is filled with the data using the pattern [db_name][username] as shown at the screenshot below. Write a query which returns usernames only (data after "" symbol) as shown at highlighted part of the screenshot

Please provide the whole query as the answer

USE AdventureWorks2019 SELECT RIGHT(LoginID,len(LoginID)-CHARINDEX('\',LoginID )) AS LoginID FROM HumanResources.Employee



Check the content of Person.Person, Person.PersonPhone and Person.PhoneNumberType tables from AdventureWorks2019 database.

Write a query that shows each person's First Name, Last Name, Phone Number, but only for the recors with "Employee" Person Type and "Work" Phone Type (please do not "hardcode" the phone type, use subquery or join instead)

Please provide the whole query as the answer * Prerequisite: run the following query in the AdventureWorks2019 database connection script:

delete from Person.EmailAddress where BusinessEntityID between 286 and 298;

Then using tables Person.Person and Person.EmailAddress write a query which will show all the fields from the Person table only for the entries which does not have an email set up in EmailAddress table.

Please provide the whole query as the answer * Prerequisite: run the following query in AdventureWorks2019 database :

Update HumanResources.JobCandidate set BusinessEntityID = 212 where jobCandidateId = 6;

Table HumanResources.JobCandidate contains info about candidates. Entries with non-NULL BusinessEntityID field already have an interviewer assigned - this BusinessEntityID represents interviewer's ID.

So, using the following tables:

HumanResources.JobCandidate HumanResources.Employee Person.Person Person.PersonPhone Person.PhoneNumberType

Write a query which returns info about all currently assigned interviewers in JobCandidate table: their FirstName and LastName, and a PhoneNumber if the type of PhoneNumber is "Work"; if it's not "Work" - default Phone Number to '8-800-555-35-35' Info about the same interviewer should appear only ONCE.

Please provide the whole query as the answer * Captionless Image This excercise uses AdventureWorksLT2019 database, not AdventureWorks2019 - be careful

Write a query which will show all Customer FN/LN from table Customers and their respective Address ID's from table CustomerAddress. In case if there's more than one address for a particluar customer in the CustomerAddress table, choose the record with AddressType = 'Main Office'. Result dataset should contain only 1 row for each Customer.

Don't be shy to use subqueries.

Please provide the whole query as the answer

False

False