Tech School Solutions

Sessions: Write a query that selects userId and average session duration for each user who has more than one session.

TABLE sessions( id INTEGER PRIMARY KEY, userId INTEGER NOT NULL, duration DECIMAL NOT NULL);

Write a query that selects userId and average session duration for each user who has more than one session.

———————————————————————————

SELECT userId, AVG(duration) as Average_Duration FROM sessions GROUP BY userId HAVING COUNT(userId) >1;

———————————————————————————

Output: Run OK and all test cases pass, great!
userId    Average_Duration    
————————–
1         12.0000             

————————————————————

Enrollment : Write a query that updates the field ‘year’ of every faulty record to 2015.

A table containing the students enrolled in a yearly course has incorrect data in records with ids between 20 and 100 (inclusive).

TABLE enrollments ( id INTEGER NOT NULL PRIMARY KEY, year INTEGER NOT NULL, studentId INTEGER NOT NULL);

Write a query that updates the field ‘year’ of every faulty record to 2015.

———————————————————————————

UPDATE enrollments SET year = 2015 WHERE id BETWEEN 20 AND 100;

———————————————————————————

Output: Run OK and all test cases pass, great!

2 rows affected.
SQL> SELECT id, year, studentId FROM enrollments;
id     year    studentId    
————————
1      2003     1            
20     2015    2            
100    2015    3            
110    2016    4    

———————————————————–  

Students: Given the following data definition, write a query that returns the number of students whose first name is John.

TABLE students id INTEGER PRIMARY KEY, firstName VARCHAR(30) NOT NULL, lastName VARCHAR(30) NOT NULL

———————————————————————————

SELECT COUNT(*) FROM students WHERE firstName =”John”;

———————————————————— Run OK and all test cases pass, great!

count(*)    
——–
2 

———————————————————————————