Skip to main content

New Page

    ACS
  • Workshop
      (SQL)

  • Question 1 (debug)

    # Foobar      เขียนโปรแกรมเพื่อหาข้อมูลที่ซ้ำกัน แก้ไขข้อมูล และกำหนด primary key

    Foobar

    
is
    1. aสร้าง Pythontable libraryจาก fordata dealingนี้
    
withwordpluralization.









    


    


    CREATE TABLE 'ws1' ('id' INTEGER ,
                                               'Age' INTEGER,
                                               'Gender' TEXT,
                                               'Education_Level' TEXT,
                                               'Job_Title' TEXT,
                                               'Years_of_Experience' TEXT,
                                               'Salary' INTEGER 
    

                                              #INPUT CODE HERE
                                              );
    INSERT INTO 'ws1' ('id','Age','Gender','Education_Level','Job_Title','Years_of_Experience','Salary') VALUES 
     ('00163','28','Female','Bachelor''s','Software Engineer','5','160000'), 
     ('00162','27','Female','Bachelor''s Degree','Software Engineer','3','70000'), 
     ('00189','28','Male','Bachelor''s Degree','Web Developer','3','55000'), 
     ('00074','25','Female','Bachelor''s Degree','Junior Software Developer','S','35000'), 
     ('00177','54','Male','PhD','Software Engineer Manager','16','190000'), 
     ('00177','54','Male','PhD','Software Engineer Manager','16','190000'), 
     ('00078','33','Male','Bachelor''s','Junior Software Developer','5','50000'), 
     ('00065','27','Female','Bachelor''s Degree','Graphic Designer','2','50000'), 
     ('00166','27','Female','Bachelor''s Degree','Software Engineer','3','70000'), 
     ('00085','27','Female','Bachelor''s Degree','Junior Software Engineer','1','45000'), 
     ('00147','45','Female','PhD','Senior UX Designer','16','160000'), 
     ('00103','30','Male','Bachelor''s','Network Engineer','3','60000'), 
     ('00139','54','Male','Master''s Degree','Senior Software Engineer','32','195270'), 
     ('00178','54','Female','PhD','Software Engineer Manager','32','191510'), 
     ('00132','38','Female','Bachelor''s','Senior Project Manager','9','120000'), 
     ('00146','40','Female','PhD','Senior Software Engineer','12','160000'), 
     ('00081','26','Female','Bachelor''s Degree','Junior Software Developer','2','35000'), 
     ('00066','27','Female','Bachelor''s Degree','Graphic Designer','2','50000'), 
     ('00107','45','Male','PhD','Senior Data Engineer','16','150000'), 
     ('00058','36','Male','Master''s Degree','Full Stack Engineer','11','137775'), 
     ('00117','36','Male','PhD','Senior Data Scientist','12','170000'), 
     ('00019','25','Female','Master''s','Data Analyst','1','100000'), 
     ('00123','41','Female','Master''s Degree','Senior Project Engineer','11','165000'), 
     ('00007','38','Male','Bachelor''s Degree','Back end Developer','14','136449'), 
     ('00109','36','Male','PhD','Senior Data Scientist','12','170000'),
     ('00025','33','Male','PhD','Data Scientist','8','190000'),
     ('00012','37','Male','Bachelor''s Degree','Data Analyst','11','160000'), 
     ('00063','28','Male','Bachelor''s Degree','Graphic Designer','3','60000'), 
     ('00136','34','Male','Master''s','Senior Software Developer','8','105000'), 
     ('00098','23','Female','Bachelor''s Degree','Junior Web Developer','1','40000'), 
     ('00069','27','Male','Bachelor''s Degree','Graphic Designer','2','50000'), 
     ('00025','33','Male','PhD','Data Scientist','8','190000'), 
     ('00131','42','Female','Bachelor''s','Senior Project Manager','12','120000'), 
     ('00184','33','Male','Bachelor''s Degree','Web Developer','5','70000'), 
     ('00144','36','Male','Master''s Degree','Senior Software Engineer','7','140000'), 
     ('00141','37','Male','Master''s Degree','Senior Software Engineer','9','145000'), 
     ('00006','46','Female','Bachelor''s Degree','Back end Developer','18','155795'), 
     ('00158','28','Male','Bachelor''s Degree','Software Developer','3','60000'), 
     ('00093','34','Male','Bachelor''s Degree','Junior Web Developer','3','50000'), 
     ('00035','29','Male','Bachelor''s Degree','Front End Developer','4','80000'), 
     ('00122','43','Male','PhD','Senior Project Engineer','13','185000'), 
     ('00154','24','Female','Bachelor''s Degree','Software Developer','2','55000'), 
     ('00037','29','Male','Bachelor''s Degree','Front End Developer','4','80000'), 
     ('00015','30','Female','Bachelor''s','Data Analyst','5','145000'), 
     ('00070','26','Female','Bachelor''s Degree','Graphic Designer','2','50000'), 
     ('00095','34','Male','Bachelor''s Degree','Junior Web Developer','3','50000'), 
     ('00038','29','Male','Bachelor''s Degree','Front End Developer','4','80000'), 
     ('00068','26','Female','Bachelor''s Degree','Graphic Designer','2','50000'), 
     ('00114','36','Male','PhD','Senior Data Scientist','12','170000'), 
     ('00051','38','Male','Bachelor''s Degree','Front end Developer','11','136062'), 
     ('00088','26','Female','Bachelor''s Degree','Junior Software Engineer','1','45000'), 
     ('00191','36','Female','Master''s Degree','Web Developer','7','85000'), 
     ('00099','24','Female','Bachelor''s Degree','Junior Web Developer','1','40000'), 
     ('00018','26','Male','Bachelor''s','Data Analyst','3','120000'), 
     ('00014','31','Male','Bachelor''s','Data Analyst','9','195000'), 
     ('00094','32','Male','Bachelor''s Degree','Junior Web Developer','2','45000'), 
     ('00143','42','Male','PhD','Senior Software Engineer','14','170000'), 
     ('00040','25','Male','Bachelor''s Degree','Front End Developer','2','50000'), 
     ('00130','39','Female','Bachelor''s','Senior Project Manager','12','130000'), 
     ('00017','25','Female','Bachelor''s','Data Analyst','2','110000'), 
     ('00153','29','Male','Bachelor''s Degree','Software Developer','5','80000'), 
     ('00174','54','Male','PhD','Software Engineer Manager','17','195000'), 
     ('00009','36','Male','Master''s Degree','Back end Developer','9','150000'), 
     ('00086','27','Female','Bachelor''s Degree','Junior Software Engineer','2','48000'), 
     ('00057','25','Other','High School','Full Stack Engineer','2','69032'), 
     ('00031','27','Male','PhD','Developer','7','100000'), 
     ('00060','29','Female','Bachelor''s Degree','Full Stack Engineer','6','103579'), 
     ('00073','25','Female','Bachelor''s Degree','Junior Software Developer','1','35000'), 
     ('00047','30','Female','Master''s Degree','Front end Developer','5','105000'), 
     ('00186','27','Female','Bachelor''s Degree','Web Developer','2','50000'), 
     ('00021','32','Female','PhD','Data Scientist','9','145000'), 
     ('00124','47','Male','PhD','Senior Project Engineer','16','190000'), 
     ('00140','44','Male','Master''s Degree','Senior Software Engineer','18','152168'), 
     ('00028','31','Male','Master''s','Data Scientist','9','195000'), 
     ('00113','33','Male','Master''s Degree','Senior Data Scientist','8','120000'), 
     ('00112','36','Male','PhD','Senior Data Scientist','12','170000'), 
     ('00089','25','Male','Bachelor''s Degree','Junior Software Engineer','1','55000'), 
     ('00142','46','Male','Master''s Degree','Senior Software Engineer','20','174938'), 
     ('00090','27','Female','Bachelor''s Degree','Junior Software Engineer','2','48000'), 
     ('00062','26','Female','Bachelor''s Degree','Graphic Designer','2','50000'), 
     ('00179','37','Female','Master''s','Software Manager','11','110000'), 
     ('00127','43','Male','PhD','Senior Project Engineer','13','185000'), 
     ('00014','31','Male','Bachelor''s','Data Analyst','9','195000'), 
     ('00039','29','Male','Bachelor''s Degree','Front End Developer','4','80000'), 
     ('00108','44','Female','PhD','Senior Data Engineer','16','160000'), 
     ('00033','29','Male','Bachelor''s Degree','Front End Developer','4','80000'), 
     ('00046','29','Male','Bachelor''s Degree','Front end Developer','3','80000'), 
     ('00148','45','Female','PhD','Senior UX Designer','16','160000'), 
     ('00168','28','Male','Bachelor''s Degree','Software Engineer','4','75000'), 
     ('00032','29','Male','Bachelor''s Degree','Front End Developer','4','80000'), 
     ('00145','31','Female','Bachelor''s Degree','Senior Software Engineer','6','104378'), 
     ('00029','31','Female','PhD','Data Scientist','8','140000'), 
     ('00004','24','Male','Bachelor''s Degree','Back end Developer','1','55000'), 
     ('00100','26','Female','Bachelor''s Degree','Junior Web Developer','2','45000'), 
     ('00135','46','Male','PhD','Senior Researcher','18','150000'), 
     ('00056','27','Female','Master''s Degree','Full Stack Engineer','3','100000'), 
     ('00165','23','Female','Bachelor''s Degree','Software Engineer','1','50000');
    



    

    2.หาข้อมูลที่ซ้ำกัน
    







    SELECT __, COUNT(*) AS duplicate_count
    FROM ____
    GROUP BY __
    HAVING COUNT(*) > 1;
    

    3. select ข้อมูลตัวที่ซ้ำกันออกมา
    







    SELECT ___ FROM  ___ WHERE ___= ___;
    

    4.delete ข้อมูลที่ซ้ำกัน
    







    DELETE ___ FROM ___ WHERE ___ = ___ ;
    

    5.กำหนด primary key 
    

     
    

     
    

     
    

    Question 2 
    

     
Back to top