Tuesday, January 15, 2019

SQL question challenge (Delete duplicates)



We will be using duplicate employees table for this demo.
Delete duplicate rows in sql
SQL Script to create dup_employees table

link to github

Create table dup_employees
(
     id int,
     first_name nvarchar(50),
     last_name nvarchar(50),
     gender nvarchar(50),
     salary int
);

Insert into dup_employees values (1, 'Mark', 'Hastings', 'Male', 60000);
Insert into dup_employees values (1, 'Mark', 'Hastings', 'Male', 60000);
Insert into dup_employees values (1, 'Mark', 'Hastings', 'Male', 60000);
Insert into dup_employees values (2, 'Mary', 'Lambeth', 'Female', 30000);
Insert into dup_employees values (2, 'Mary', 'Lambeth', 'Female', 30000);
Insert into dup_employees values (3, 'Ben', 'Hoskins', 'Male', 70000);
Insert into dup_employees values (3, 'Ben', 'Hoskins', 'Male', 70000);
Insert into dup_employees values (3, 'Ben', 'Hoskins', 'Male', 70000);

Wednesday, January 9, 2019

SQL question challenge (Messages)


Link to github

Table: date | u1 | u2 | n_msg
n_msg: the number of messsages between one unique user pair at someday.

DDL Scripts
create table messages(
  date varchar(80),
  u1 varchar(80),
  u2 varchar(80),
  n_msg integer
 );
 
 insert into messages values ('10/01/2018','user1','user2',4);
 insert into messages values ('10/01/2018','user2','user1',4);
 insert into messages values ('10/01/2018','user1','user4',2);
 insert into messages values ('10/02/2018','user1','user2',2);
 insert into messages values ('10/02/2018','user2','user1',1);
 insert into messages values ('10/02/2018','user3','user4',10);
 insert into messages values ('10/02/2018','user3','user2',14);
 insert into messages values ('10/02/2018','user1','user2',14);
 insert into messages values ('10/03/2018','user4','user1',4);
 insert into messages values ('10/03/2018','user4','user5',25);

SQL question challenge (Friends)

Link to Github

DDL Scripts


create table table1(time date,user_id int,app varchar(40),event varchar(40));

 insert into table1 values('2018-10-01',1,'facebook','processed');
 insert into table1 values('2018-10-01',1,'facebook','sent');
 insert into table1 values('2018-10-01',1,'facebook','accepted');
 insert into table1 values('2018-10-01',2,'facebook','sent');
 insert into table1 values('2018-10-02',2,'facebook','accepted');
 insert into table1 values('2018-10-01',3,'facebook','sent');

 select * from table1;
 +------------+---------+----------+-----------+
 | time       | user_id | app      | event     |
 +------------+---------+----------+-----------+
 | 2018-10-01 |       1 | facebook | processed |
 | 2018-10-01 |       1 | facebook | sent      |
 | 2018-10-01 |       1 | facebook | accepted  |
 | 2018-10-01 |       2 | facebook | sent      |
 | 2018-10-02 |       2 | facebook | accepted  |
 | 2018-10-01 |       3 | facebook | sent      |
 +------------+---------+----------+-----------+

SQL question challenge (Continents)


Link to github

Table with the columns: continents: continent, country, population
CREATE TABLE continents (
   continent          varchar(80),
   country            varchar(80),
   population         int
  );

  INSERT INTO continents VALUES ('Asia','China', 100);
  INSERT INTO continents VALUES ('Asia','India', 100);
  INSERT INTO continents VALUES ('Africa','South Africa', 50);
  INSERT INTO continents VALUES ('Africa','Egypt', 20);
  INSERT INTO continents VALUES ('North America','USA', 50);
  INSERT INTO continents VALUES ('North America','Canada', 50);

SQL question challenge (Contents)


Link to github

  Table: content_id | content_type (comment/ post) | target_id
           If it is comment,target_id is the userid who posts it.
           If it is post, then target_id is NULL.

DDL Scripts
CREATE TABLE contents(
  content_id int,
  content_type varchar(80),
  target_id varchar(80));

  INSERT INTO contents VALUES (1,'comment','u1');
  INSERT INTO contents VALUES (2,'post',NULL);
  INSERT INTO contents VALUES (3,'comment','u2');
  INSERT INTO contents VALUES (4,'post',NULL);
  INSERT INTO contents VALUES (5,'comment','u1');
  INSERT INTO contents VALUES (6,'comment','u1');
  INSERT INTO contents VALUES (7,'comment','u3');
  INSERT INTO contents VALUES (8,'post',NULL);
  INSERT INTO contents VALUES (9,'video','u1');
  INSERT INTO contents VALUES (10,'video','u3');
  INSERT INTO contents VALUES (11,'post',NULL);
  INSERT INTO contents VALUES (12,'photo','u1');
  INSERT INTO contents VALUES (13,'photo','u3');
  INSERT INTO contents VALUES (14,'photo','u1');
  INSERT INTO contents VALUES (15,'article','u3');

SQL question challenge (Companies)


Link to github

DDL Scripts
CREATE TABLE companies (
   member_id            int,
   company_name       varchar(80),
   year_start      int
  );

  INSERT INTO companies VALUES (1,'Google', 1990);
  INSERT INTO companies VALUES (1,'Microsoft', 2000);
  INSERT INTO companies VALUES (2,'Microsoft', 2000);
  INSERT INTO companies VALUES (2,'Google', 2001);
  INSERT INTO companies VALUES (3,'Microsoft', 1997);
  INSERT INTO companies VALUES (3,'Google', 1998);
  INSERT INTO companies VALUES (4,'Microsoft', 1997);
  INSERT INTO companies VALUES (4,'LinkedIn', 1998);
  INSERT INTO companies VALUES (4,'Google', 2000);
  +-----------+--------------+------------+
  | member_id | company_name | year_start |
  +-----------+--------------+------------+
  |         1 | Google       |       1990 |
  |         1 | Microsoft    |       2000 |
  |         2 | Microsoft    |       2000 |
  |         2 | Google       |       2001 |
  |         3 | Microsoft    |       1997 |
  |         3 | Google       |       1998 |
  |         4 | Microsoft    |       1997 |
  |         4 | LinkedIn     |       1998 |
  |         4 | Google       |       2000 |
  +-----------+--------------+------------+

SQL question challenge (Comments)

Link to Github

DDL Scripts
CREATE TABLE comments (
  name            varchar(80),
  posts           varchar(80),
  comments         int
 );

 INSERT INTO comments VALUES ('u1', 'page1', '90');
 INSERT INTO comments VALUES ('u1', 'page2', '50');
 INSERT INTO comments VALUES ('u1', 'page3', '40');
 INSERT INTO comments VALUES ('u2', 'page2', '55');
 INSERT INTO comments VALUES ('u2', 'page4', '45');
 INSERT INTO comments VALUES ('u4', 'page4', '30');
 INSERT INTO comments VALUES ('u4', 'page3', '40');
 INSERT INTO comments VALUES ('u3', 'page2', '100');

 Comments I
 name posts comments
 u1 page1 90
 u1 page2 50
 u1 page3 40
 u2 page2 55
 u2 page4 45
 u4 page4 30
 u4 page3 40
 u3 page2 100

SQL question challenge (Article views)

Link to github

DDL Scripts
CREATE TABLE article_views (
   date            timestamp,
   viewer_id       int,
   article_id      int,
   author_id       int
  );
  
  INSERT INTO article_views VALUES ('2017-08-01',123, 456 ,789);
  INSERT INTO article_views VALUES ('2017-08-02',432 ,543, 654);
  INSERT INTO article_views VALUES ('2017-08-01',789, 456 ,789);
  INSERT INTO article_views VALUES ('2017-08-03',567, 780, 432);
  INSERT INTO article_views VALUES ('2017-08-01',789, 457    ,789);
     
  date        viewer_id        article_id      author_id
  2017-08-01 123   456   789
  2017-08-02 432   543   654
  2017-08-01 789   456   789
  2017-08-03 567   780   432
  2017-08-01 789   457   789 

SQL question challenge (Ads conversion)


Link to Github
Here we have 2 tables

adv_info: advertiser_id | ad_id | spend: The Advertiser pay for this ad

ad_info: ad_id | user_id | price: The user spend through this ad (Assume all prices in this column >0)

DDL Scripts


  CREATE TABLE adv_info (
  advertiser_id    int,
  ad_id    int,
  spend    float
 );

 INSERT INTO adv_info VALUES (10, 200,300);
 INSERT INTO adv_info VALUES (11, 100,1000);
 INSERT INTO adv_info VALUES (13, 400,3000);
 INSERT INTO adv_info VALUES (14, 500,5000);

 CREATE TABLE ad_info (
  ad_id    int,
  user_id    int,
  price    float
 );

 INSERT INTO ad_info VALUES (200, 4,30);
 INSERT INTO ad_info VALUES (100, 4,100);
 INSERT INTO ad_info VALUES (400, 4,300);
 INSERT INTO ad_info VALUES (200, 10,31);
 INSERT INTO ad_info VALUES (100, 10,110);
 INSERT INTO ad_info VALUES (400, 10,310);

 select * 
  from ad_info;
  +-------+---------+-------+
  | ad_id | user_id | price |
  +-------+---------+-------+
  |   200 |       4 |    30 |
  |   100 |       4 |   100 |
  |   400 |       4 |   300 |
  |   200 |      10 |    31 |
  |   100 |      10 |   110 |
  |   400 |      10 |   310 |
  +-------+---------+-------+
  
 select * 
  from adv_info;
  +---------------+-------+-------+
  | advertiser_id | ad_id | spend |
  +---------------+-------+-------+
  |            10 |   200 |   300 |
  |            11 |   100 |  1000 |
  |            13 |   400 |  3000 |
  |            14 |   500 |  5000 |
  +---------------+-------+-------+
 
 

Tuesday, January 8, 2019

Cheat sheet for echo


The syntax for echo is:
echo [option(s)] [string(s)]
1. Input a line of text and display on standard output
$ echo Tecmint is a community of Linux Nerds 
Outputs the following text:
Tecmint is a community of Linux Nerds 

Cheat sheet for cat command


Display content of the file
cat /etc/passwd

View contents of the multiple files in the terminal
cat sample1 sample2
First sample
This is the second sample;

Create file with cat ctrl+d to exit
cat >testcat
Testing file with cat command


cheat sheet for ls



1. List Files using ls with no option
ls with no option list files and directories in bare format where we won’t be able to view details like file types, size, modified date and time, permission and links etc.


[vn04nxr@client-236651385-1-270480917 ~]$ ls
affiliate_spend_viq_08-13-2018_to_08-23-2018.tsv  employee    first_file  
2 List Files With option –l 
Here, ls -l (-l is character not one) shows file or directory, size, modified date and time, file or folder name and owner of file and its permission.
[vn04nxr@client-236651385-1-270480917 ~]$ ls -l
total 64
-rw-rw-r-- 1 vn04nxr vn04nxr    22 Aug 23 19:11 affiliate_spend_viq_08-13-2018_to_08-23-2018.tsv
-rw-rw-r-- 1 vn04nxr vn04nxr 14510 Sep  3 14:58 cat_more
-rw-rw-r-- 1 vn04nxr vn04nxr    35 Aug 23 21:46 employee

SQL question challenge (Employee Dept)

Link to Github

These are the list of tables which includes employees, projects and department table. It also shows the relationship between these tables
employees                             projects
  +---------------+---------+           +---------------+---------+
  | id            | int     |<----+  +->| id            | int     |
  | first_name    | varchar |     |  |  | title         | varchar |
  | last_name     | varchar |     |  |  | start_date    | date    |
  | salary        | int     |     |  |  | end_date      | date    |
  | department_id | int     |--+  |  |  | budget        | int     |
  +---------------+---------+  |  |  |  +---------------+---------+
          |  |  |
  departments                  |  |  |  employees_projects
  +---------------+---------+  |  |  |  +---------------+---------+
  | id            | int     |<-+  |  +--| project_id    | int     |
  | name          | varchar |     +-----| employee_id   | int     |
  +---------------+---------+           +---------------+---------+

1) SELECT EMPLOYEES FROM
     DEPARTMENT WHERE SALARY IS GREATER THAN 40K

select concat(employees.first_name,' ',employees.last_name) as employee_name,names 
  from employees,departments 
  where employees.department_id=departments.id 
  group by employees.id,employees.salary 
  having employees.salary > 40000;

SQL question challenge (Employee Boss)



DDL Scripts :

Employee table
 create table employee(employee_id integer,department_id integer,boss_id 
         integer,name varchar(50),salary integer);
 
 insert into employee values(1,1,2,'John Smith',20000);
 insert into employee values(2,5,4,'Ava Muffinson',10000);
 insert into employee values(3,2,5,'Cailin Ninson',30000);
 insert into employee values(4,2,5,'Mike Peterson',20000);
 insert into employee values(5,2,1,'Ian Peterson',80000);
 insert into employee values(6,3,null,'John Mills',50000);

 +----+------------+-----------+--------+---------------+
 | id | first_name | last_name | salary | department_id |
 +----+------------+-----------+--------+---------------+
 |  1 | John       | Smith     |  20000 |             1 |
 |  2 | Ava        | Muffinson |  10000 |             5 |
 |  3 | Cailin     | Ninson    |  30000 |             2 |
 |  4 | Mike       | Peterson  |  20000 |             2 |
 |  5 | Ian        | Peterson  |  80000 |             2 |
 |  6 | John       | Mills     |  50000 |             3 |
 +----+------------+-----------+--------+---------------+