commit 700bb4f70ed39ffe91bca312cd9f99d9814c1084 parent 36aa22af7df23e097eaf90fb809250fdb958ba84 Author: Christos Margiolis <christos@margiolis.net> Date: Mon, 7 Jun 2021 01:06:48 +0300 java... Diffstat:
19 files changed, 409 insertions(+), 142 deletions(-)
diff --git a/cpp_oop/game/Engine.cc b/cpp_oop/game/Engine.cc @@ -24,10 +24,10 @@ Engine::Engine(const char *mapfile, const char *scorefile) try { load_map(mapfile); score = new Score(scorefile); - } catch (const std::runtime_error& e) { - throw std::runtime_error("error: " + std::string(e.what())); } catch (const std::ios_base::failure& e) { throw std::runtime_error("error: " + std::string(e.what())); + } catch (const std::runtime_error& e) { + throw std::runtime_error("error: " + std::string(e.what())); } if (!init_gamewin()) @@ -107,8 +107,8 @@ Engine::init_curses() colors.push_back(COLOR_CYAN); /* Potter */ colors.push_back(COLOR_GREEN); /* Gnome */ colors.push_back(COLOR_YELLOW); /* Traal */ - colors.push_back(COLOR_MAGENTA);/* Stone */ - colors.push_back(COLOR_WHITE); /* Parchment */ + colors.push_back(COLOR_WHITE); /* Stone */ + colors.push_back(COLOR_BLACK); /* Parchment */ start_color(); use_default_colors(); @@ -160,7 +160,7 @@ Engine::load_map(const char *mapfile) while (std::getline(f, str)) { /* * If a row happens to have a different length, the map hasn't - * been written properly, so we exit. All rows have be + * been written properly, so we exit. All rows have to * have the same length. */ if (l != str.length()) @@ -278,7 +278,8 @@ Engine::popup(const std::vector<std::string>& lines) const auto lencmp = [](const std::string& a, const std::string& b) { return a.length() < b.length(); }; - int vecsz, wr, wc, wy, wx; + std::size_t vecsz; + int wr, wc, wy, wx; vecsz = lines.size(); /* @@ -334,6 +335,7 @@ Engine::kbd_input() { int key, dir, newx, newy; + dir = 0; newx = player->get_x(); newy = player->get_y(); diff --git a/cpp_oop/game/Engine.hpp b/cpp_oop/game/Engine.hpp @@ -1,6 +1,7 @@ #ifndef _ENGINE_HPP_ #define _ENGINE_HPP_ +#include <algorithm> #include <cmath> #include <csignal> #include <cstdlib> @@ -32,7 +33,7 @@ #define SYM_POTTER 'P' #define SYM_GNOME 'G' #define SYM_TRAAL 'T' -#define SYM_STONE 'S' +#define SYM_STONE '+' #define SYM_PARCHMENT 'O' class Engine { @@ -54,8 +55,8 @@ private: int wymax; int w; int h; - int nenemies = 3; - int ngems = 2; + int nenemies = 2; + int ngems = 10; volatile sig_atomic_t f_running; public: diff --git a/cpp_oop/game/main.cc b/cpp_oop/game/main.cc @@ -1,5 +1,3 @@ -#include <cstring> - #include "Engine.hpp" /* Program name */ diff --git a/cpp_oop/game/res/map1 b/cpp_oop/game/res/map1 @@ -1,41 +1,20 @@ -********************************************************************************************************************************************** -********************** ****************************************************************************************************** -*************************************** ************************ ************************************************************************** -*************************************** ****************** ***************************************************************************** -*************************************** ************ *********************************************************************************** -* **************** ****** **************************************************************************************** -********************* **************** ** ************************ ********************** -********************* **************** ***************************** ********************************** *********** ********************** -********** **************** ******************************** ********************************** *********** ********************** -********************** **************** ******************************** ********************************** *********** ********************** -********************** **************** ******************************* ********************************** *********** ********************** -********************** ***************** ********************************** *********** ********************** -********************** ************************************************* ********************************** *********** ********************** -********************** ************************************************* ********************************** *********** ********************** -********************** ************************************************* ********************************** *********** ********************** -********************** ************************************************* ********************************** *********** ********************** -********************** ************************************************* ********* *********** ********************** -********************** ********* ************************ *********** -*********************** ********** *********************************************** ************************ ********************************** -*********************** ********** *********************************************** ************************ ********************************** -*********************** ********** *********************************************** ************************ ********************************** -************ ********** ******************** ********************************** -************ ********************* ******************** ************************************************************************************** -************ ********************* ******************* ************************************************************************************** -************ ********************* ************************************************************************************** -************ ****************************************** ************************************************************************************** -************ ****************************************** ************************************************************************************** -************ ****************************************** ************************************************************************************** -************ *************************** *** ************************************************************************************** -************ *************************** * ******* ************************************************************************************** -************ *************************** ************** ********************************* -************ *************************** ******************************************************************* ********************************* -************ *************************** ******************************************************************* ********************************* -************ *************************** ******************************************************************* ********************************* -************ *************************** ******************************************************************* ********************************* -************ ******************************************************************* ********************************* -************************************************************************************************************ ********************************* -************************************************************************************************************ ********************************* -************************************************************************************************************ ********************************* -************************************************************************************************************ ********************************* -************************************************************************************************************ ********************************* +********************************************************************* +* * +* * +********* ******* *** ******* ***** ******** +* * * * ************ * +* * * * * * * * +* * +* * * * ***** * +************************ * * * * * +* * ************** * * * +* ********************* * * * * +* * * * * * +* **** *** * * * * * +* * * * * * * * * +* * * * * * * * +* * * * * * ******** +* * * *** ****** * * * +* ********** ***** ******** * * +* * * +********************************************************************* diff --git a/cpp_oop/game/res/map2 b/cpp_oop/game/res/map2 @@ -1,20 +1,20 @@ ********************************************************************* * * -* * -********* ******* *** ******* ***** ******** -* * * * ************ * -* * * * * * * * -* * -* * * * ***** * -************************ * * * * * -* * ************** * * * -* ********************* * * * * -* * * * * * -* **** *** * * * * * -* * * * * * * * * -* * * * * * * * -* * * * * * ******** -* * * *** ****** * * * -* ********** ***** ******** * * +* ***************** * +**************************** * * * +* * * * ***** ***** ***** ** +* * * * * * * +* ******* ****** ******* * * * * * +* * * * * * * +************************ * * * * * * +* * ****** * * * +* * ************************ * * * * * +* * * * * * +* * ****** *** ************* *** * * +* * * * * * * * +* * * * * * * * +* * * * * * * * +* * * * * * * * +* ********** ********** ************* * * * * * * ********************************************************************* diff --git a/cpp_oop/game/res/map3 b/cpp_oop/game/res/map3 @@ -1,20 +0,0 @@ -********************************************************************* -* * -* ***************** * -**************************** * * * -* * * * ***** ***** ***** ** -* * * * * * * -* ******* ****** ******* * * * * * -* * * * * * * -************************ * * * * * * -* * ****** * * * -* * ************************ * * * * * -* * * * * * -* * ****** *** ************* *** * * -* * * * * * * * -* * * * * * * * -* * * * * * * * -* * * * * * * * -* ********** ********** ************* * * * -* * * -********************************************************************* diff --git a/java_development/population/.classpath b/java_development/population/.classpath @@ -1,7 +1,7 @@ <?xml version="1.0" encoding="UTF-8"?> <classpath> <classpathentry kind="src" path="src"/> - <classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.7"> + <classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8"> <attributes> <attribute name="module" value="true"/> </attributes> diff --git a/java_development/population/.settings/org.eclipse.jdt.core.prefs b/java_development/population/.settings/org.eclipse.jdt.core.prefs @@ -1,8 +1,8 @@ eclipse.preferences.version=1 org.eclipse.jdt.core.compiler.codegen.inlineJsrBytecode=enabled -org.eclipse.jdt.core.compiler.codegen.targetPlatform=1.7 -org.eclipse.jdt.core.compiler.compliance=1.7 +org.eclipse.jdt.core.compiler.codegen.targetPlatform=1.8 +org.eclipse.jdt.core.compiler.compliance=1.8 org.eclipse.jdt.core.compiler.problem.assertIdentifier=error org.eclipse.jdt.core.compiler.problem.enumIdentifier=error org.eclipse.jdt.core.compiler.release=disabled -org.eclipse.jdt.core.compiler.source=1.7 +org.eclipse.jdt.core.compiler.source=1.8 diff --git a/java_development/population/bin/population/Column.class b/java_development/population/bin/population/Column.class Binary files differ. diff --git a/java_development/population/bin/population/Country.class b/java_development/population/bin/population/Country.class Binary files differ. diff --git a/java_development/population/bin/population/ExcelParser.class b/java_development/population/bin/population/ExcelParser.class Binary files differ. diff --git a/java_development/population/bin/population/Main.class b/java_development/population/bin/population/Main.class Binary files differ. diff --git a/java_development/population/src/population/Column.java b/java_development/population/src/population/Column.java @@ -0,0 +1,19 @@ +package population; + +public class Column { + private String name; + private String fieldname; + + Column(String name, String fieldname) { + this.name = name; + this.fieldname = fieldname; + } + + public String get_name() { + return name; + } + + public String get_fieldname() { + return fieldname; + } +} diff --git a/java_development/population/src/population/Country.java b/java_development/population/src/population/Country.java @@ -3,7 +3,7 @@ package population; import java.util.HashMap; public class Country { - private String index;; + private String index; private String variant = ""; private String name = ""; private String notes = ""; @@ -11,7 +11,7 @@ public class Country { private String type = ""; private String pntcode; private HashMap<Integer, Integer> population; - private Integer recentpop; + private String recentpop; Country(String index, String variant, String name, String notes, String ctrycode, String type, String pntcode, @@ -24,38 +24,47 @@ public class Country { this.type = type; this.pntcode = pntcode; this.population = population; - recentpop = population.get(population.size()); + recentpop = String.valueOf( + population.get(Main.STARTING_YEAR + population.size() - 1)); } - public String get_index() { + /* + * The getters *must* have this naming convention, otherwise + * JavaFX TableView methods don't recognize them. Pretty stupid... + */ + public String getIndex() { return index; } - public String get_variant() { + public String getVariant() { return variant; } - public String get_name() { + public String getName() { return name; } - public String get_notes() { + public String getNotes() { return notes; } - public String get_ctrycode() { + public String getCtrycode() { return ctrycode; } - public String get_type() { + public String getType() { return type; } - public String get_pntcode() { + public String getPntcode() { return pntcode; } - public HashMap<Integer, Integer> get_population() { + public HashMap<Integer, Integer> getPopulation() { return population; } + + public String getRecentpop() { + return recentpop; + } } diff --git a/java_development/population/src/population/ExcelParser.java b/java_development/population/src/population/ExcelParser.java @@ -13,9 +13,7 @@ public class ExcelParser { private final String EST_SHEET = "ESTIMATES"; private final String TARGET_TYPE = "Country/Area"; private final int TARGET_TYPE_CELL = 5; - private final int HEADER_ROW = 16; - private final int COUNTRIES_ROW = 44; - private final int STARTING_YEAR = 1950; + private final int COUNTRIES_ROW = 43; private List<Country> countries; private XSSFRow row; @@ -37,9 +35,7 @@ public class ExcelParser { row = (XSSFRow)rit.next(); rownum = row.getRowNum(); cv = row.getCell(TARGET_TYPE_CELL).getStringCellValue(); - /* TODO: Separate header */ - if (rownum == HEADER_ROW - || (rownum>= COUNTRIES_ROW && cv.equals(TARGET_TYPE))) + if (rownum>= COUNTRIES_ROW && cv.equals(TARGET_TYPE)) countries.add(read_country(row)); } wbook.close(); @@ -51,10 +47,9 @@ public class ExcelParser { Country ctry; Iterator<Cell> cit; Cell cell; - Integer year = STARTING_YEAR; + Integer year = Main.STARTING_YEAR; String[] fields = new String[MIN_CELLNUM]; HashMap<Integer, Integer> population = new HashMap<Integer, Integer>(); - Double d; Integer n; int i = 0; @@ -95,22 +90,25 @@ public class ExcelParser { break; } } else if (cell.getCellType() == CellType.NUMERIC) + /* + * Population numbers need to be multiplied by + * 1000 in order to convert them properly to + * Integer. + */ population.put(year++, - dtoi(cell.getNumericCellValue())); + dtoi(cell.getNumericCellValue() * 1000)); } ctry = new Country(fields[0], fields[1], fields[2], fields[3], - fields[4], fields[5], fields[5], population); + fields[4], fields[5], fields[6], population); return ctry; } private Integer dtoi(double n) { - Integer i; Double d; d = Double.valueOf(n); - i = Integer.valueOf(d.intValue()); - return i; + return Integer.valueOf(d.intValue()); } public List<Country> get_countries() { diff --git a/java_development/population/src/population/Main.java b/java_development/population/src/population/Main.java @@ -1,23 +1,40 @@ package population; import java.io.*; +import java.util.ArrayList; +import java.util.Arrays; import java.util.List; import javafx.application.Application; +import javafx.application.Platform; +import javafx.collections.FXCollections; +import javafx.collections.ObservableList; +import javafx.geometry.Insets; +import javafx.geometry.Pos; import javafx.scene.Scene; +import javafx.scene.control.Button; import javafx.scene.control.Label; -import javafx.scene.layout.StackPane; +import javafx.scene.control.TableColumn; +import javafx.scene.control.TableRow; +import javafx.scene.control.TableView; +import javafx.scene.control.cell.PropertyValueFactory; +import javafx.scene.layout.HBox; +import javafx.scene.layout.VBox; import javafx.stage.Stage; public class Main extends Application { + public static final int STARTING_YEAR = 1950; private final String APP_TITLE = "Population Statistics"; List<Country> countries; @Override - public void start(Stage primstage) throws Exception { + public void start(Stage stg) throws Exception { Parameters params; List<String> args; ExcelParser ep; String path = ""; + VBox vb; + HBox btns; + TableView<Country> tbv; /* Parse Excel file. */ params = getParameters(); @@ -26,29 +43,116 @@ public class Main extends Application { path = args.get(0); ep = new ExcelParser(path); } catch (FileNotFoundException e) { - err(primstage, path + ": no such file"); + err(stg, path + ": no such file"); return; } catch (IndexOutOfBoundsException e) { - err(primstage, "usage: population xlsx_file"); + err(stg, "usage: population xlsx_file"); return; } catch (IOException e) { - err(primstage, path + ": io error"); + err(stg, path + ": io error"); return; } countries = ep.get_countries(); /* Set up GUI. */ + tbv = mktable(stg); + btns = mkbtns(); + vb = mkvbox(tbv, btns); + stg.setTitle(APP_TITLE); + stg.setScene(new Scene(vb)); + stg.show(); } - /* TODO: make it an actual popup error window. */ - public void err(Stage stg, String errstr) { + /* TODO: make it an actual popup error window? */ + private void err(Stage stg, String errstr) { Label lbl = new Label(errstr); - Scene scene = new Scene(new StackPane(lbl), 300, 200); + lbl.setAlignment(Pos.CENTER); + Scene scene = new Scene(lbl); stg.setScene(scene); stg.show(); } - + + private HBox mkbtns() { + HBox hb; + Button btn_chart; + Button btn_exit; + + hb = new HBox(); + btn_exit = new Button("Exit"); + btn_exit.setOnAction(value -> { + Platform.exit(); + System.exit(0); + }); + btn_chart = new Button("Chart"); + btn_chart.setOnAction(value -> { + mkchart(); + }); + hb.getChildren().addAll(btn_chart, btn_exit); + hb.setSpacing(5); + hb.setAlignment(Pos.CENTER); + return hb; + } + + private TableView<Country> mktable(Stage stg) { + TableView<Country> tbv; + TableColumn<Country, String> tbc; + List<Column> cols; + ObservableList<Country> addrs; + + cols = new ArrayList<Column>(Arrays.asList( + new Column("Index", "index"), + new Column("Variant", "variant"), + new Column("Country", "name"), + new Column("Notes", "notes"), + new Column("Country Code", "ctrycode"), + new Column("Type", "type"), + new Column("Parent Code", "pntcode"), + new Column("Population", "recentpop") + )); + tbv = new TableView<Country>(); + addrs = FXCollections.observableArrayList(countries); + for (Column col : cols) { + tbc = new TableColumn<Country, String>(col.get_name()); + tbc.setCellValueFactory( + new PropertyValueFactory<Country, String>( + col.get_fieldname())); + tbv.getColumns().add(tbc); + } + tbv.setRowFactory(tv -> { + TableRow<Country> row = new TableRow<Country>(); + row.setOnMouseClicked(ev -> { + if (ev.getClickCount() == 2 && !row.isEmpty()) { + Country c = row.getItem(); + /* TODO: implement toast */ + System.out.println( + c.getIndex() + "," + + c.getName() + "," + + c.getRecentpop()); + } + }); + return row; + }); + tbv.setItems(addrs); + tbv.prefWidthProperty().bind(stg.widthProperty()); + tbv.prefHeightProperty().bind(stg.heightProperty()); + return tbv; + } + + private VBox mkvbox(TableView<Country> tbv, HBox btns) { + VBox vb; + + vb = new VBox(); + vb.setSpacing(5); + vb.setPadding(new Insets(10, 10, 10, 10)); + vb.getChildren().addAll(tbv, btns); + return vb; + } + + private void mkchart() { + + } + public static void main(String[] args) { launch(args); } diff --git a/sql_databases1/ex1_new_personnel.sql b/sql_databases1/ex1_new_personnel.sql @@ -9,7 +9,7 @@ CREATE TABLE EMP ( EMPNO INT(2) NOT NULL, ENAME VARCHAR, JOB VARCHAR, - HIERDATE DATE, + HIREDATE DATE, MGR INT(2), SAL FLOAT(7,2), COMM FLOAT(7,2), @@ -85,7 +85,7 @@ SELECT * FROM ASSIGN; * EMPNO INT(2) NOT NULL, * ENAME VARCHAR, * JOB VARCHAR, - * HIERDATE DATE, + * HIREDATE DATE, * MGR INT(2), * SAL FLOAT(7,2), * COMM FLOAT(7,2), @@ -112,7 +112,7 @@ SELECT * FROM ASSIGN; * 20 SALES LONDON * 30 RESEARCH ATHENS * 40 PAYROLL LONDON - * EMPNO ENAME JOB HIERDATE MGR SAL COMM DEPTNO + * EMPNO ENAME JOB HIREDATE MGR SAL COMM DEPTNO * ----- ------- ---------- -------- --- ------ ----- ------ * 10 CODD ANALYST 1/1/89 15 3000.0 10 * 15 ELMASRI ANALYST 2/5/95 15 1200.0 150.0 10 diff --git a/sql_databases1/ex2_new_personnel.sql b/sql_databases1/ex2_new_personnel.sql @@ -9,7 +9,7 @@ CREATE TABLE EMP ( EMPNO INT(2) NOT NULL, ENAME VARCHAR, JOB VARCHAR, - HIERDATE DATE, + HIREDATE DATE, MGR INT(2), SAL FLOAT(7,2), COMM FLOAT(7,2), @@ -62,36 +62,41 @@ INSERT INTO ASSIGN VALUES .mode column -- Question 1 -SELECT ENAME, - SAL AS ΜΙΣΘΟΣ, - COMM AS ΠΡΟΜΗΘΕΙΑ, - IFNULL(ROUND(COMM * 100 / SAL, 1) || "%", "0.00%") AS ΠΟΣΟΣΤΟ +SELECT + ENAME, + SAL AS "ΜΙΣΘΟΣ", + COMM AS "ΠΡΟΜΗΘΕΙΑ", + IFNULL(ROUND(COMM * 100 / SAL, 1) || "%", "0.00%") AS "ΠΟΣΟΣΤΟ" FROM EMP; -- Question 2 -SELECT ENAME AS ΕΠΩΝΥΜΟ, +SELECT + ENAME AS "ΕΠΩΝΥΜΟ", SAL AS "ΜΗΝΙΑΙΕΣ ΑΠΟΔΟΧΕΣ", - strftime("%Y", "now") - strftime("%Y", HIERDATE) AS ПРОΥΠΗΡΕΣΙΑ + strftime("%Y", "now") - strftime("%Y", HIREDATE) AS "ПРОΥΠΗΡΕΣΙΑ" FROM EMP; -- Question 3 -SELECT ENAME AS ΕΠΩΝΥΜΟ, - JOB AS ΘΕΣΗ, - HIERDATE AS ΠΡΟΣΛΗΨΗ +SELECT + ENAME AS "ΕΠΩΝΥΜΟ", + JOB AS "ΘΕΣΗ", + HIREDATE AS "ΠΡΟΣΛΗΨΗ" FROM EMP - WHERE strftime("%d", HIERDATE) <= "05"; + WHERE strftime("%d", HIREDATE) <= "05"; -- Question 4 -SELECT ENAME AS ΕΠΩΝΥΜΟ +SELECT + ENAME AS "ΕΠΩΝΥΜΟ" FROM EMP INNER JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO AND DEPT.DNAME = "ACCOUNTING"; -- Question 5 -SELECT ENAME AS ΕΠΩΝΥΜΟ, +SELECT + ENAME AS "ΕΠΩΝΥΜΟ", MAX( - (strftime("%Y", "now") - strftime("%Y", HIERDATE)) * + (strftime("%Y", "now") - strftime("%Y", HIREDATE)) * 12 * SAL ) AS "ΣΥΝΟΛΙΚΟΣ ΜΙΣΘΟΣ" FROM EMP; diff --git a/sql_databases1/ex3_new_personnel.sql b/sql_databases1/ex3_new_personnel.sql @@ -0,0 +1,172 @@ +CREATE TABLE DEPT ( + DEPTNO INT(2) NOT NULL, + DNAME VARCHAR, + LOC VARCHAR, + PRIMARY KEY(DEPTNO) +); + +CREATE TABLE EMP ( + EMPNO INT(2) NOT NULL, + ENAME VARCHAR, + JOB VARCHAR, + HIREDATE DATE, + MGR INT(2), + SAL FLOAT(7,2), + COMM FLOAT(7,2), + DEPTNO INT(2) NOT NULL, + PRIMARY KEY(EMPNO), + FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) +); + +CREATE TABLE PROJ ( + PROJ_CODE INT(3) NOT NULL, + DESCRIPTION VARCHAR, + PRIMARY KEY(PROJ_CODE) +); + +CREATE TABLE ASSIGN ( + EMPNO INT(2) NOT NULL, + PROJ_CODE INT(3), + A_TIME INT(3), + PRIMARY KEY(EMPNO, PROJ_CODE), + FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO), + FOREIGN KEY(PROJ_CODE) REFERENCES PROJ(PROJ_CODE) +); + +INSERT INTO DEPT VALUES + (10, 'ACCOUNTING', 'ATHENS'), + (20, 'SALES', 'LONDON'), + (30, 'RESEARCH', 'ATHENS'), + (40, 'PAYROLL', 'LONDON'); + +INSERT INTO EMP VALUES + (10, 'CODD', 'ANALYST', '1989-01-01', 15, 3000, NULL, 10), + (15, 'ELMASRI', 'ANALYST', '1995-05-02', 15, 1200, 150, 10), + (20, 'NAVATHE', 'SALESMAN', '1977-07-07', 20, 2000, NULL, 20), + (25, 'SMITH', 'PROGRAMMER', '2000-01-04', 30, 2800, 200, 30), + (30, 'DATE', 'PROGRAMMER', '2004-05-04', 15, 1800, 200, 10); + +INSERT INTO PROJ VALUES + (100, 'PAYROLL'), + (200, 'PERSONELL'), + (300, 'SALES'); + +INSERT INTO ASSIGN VALUES + (10, 100, 40), + (10, 200, 60), + (15, 100, 100), + (20, 200, 100), + (30, 100, 100); + +-- Using SQLite3 syntax +.header on +.mode column + +-- Question 1 +SELECT + ENAME, DEPTNO FROM EMP + WHERE DEPTNO = 10 + ORDER BY SAL DESC; + +-- Question 2 +SELECT + ENAME, JOB, SAL FROM EMP + ORDER BY JOB ASC, SAL DESC; + +-- Question 3 +SELECT + DEPTNO AS "ΤΜΗΜΑ", + AVG(SAL) AS "ΜΕΣΟΣ ΜΙΣΘΟΣ" + FROM EMP + GROUP BY DEPTNO + HAVING COUNT(*) >= 1; + +-- Question 4 +SELECT + DEPTNO AS "ΤΜΗΜΑ", + AVG(strftime("%Y", "now") - strftime("%Y", HIREDATE)) AS "ПРОΥΠΗΡΕΣΙΑ (ετη)" + FROM EMP + GROUP BY DEPTNO; + +-- Question 5 +SELECT + PROJ.DESCRIPTION AS PNAME, + EMP.ENAME, + EMP.JOB + FROM EMP, PROJ + INNER JOIN DEPT + ON EMP.DEPTNO = DEPT.DEPTNO AND + DEPT.DNAME = PROJ.DESCRIPTION + ORDER BY PNAME, EMP.JOB ASC; + +-- Question 6 +SELECT + DEPT.DNAME AS Department, + mgr.ENAME AS Manager, + empl.ENAME AS Employee + FROM EMP empl + INNER JOIN EMP mgr + ON empl.MGR = mgr.EMPNO, + DEPT WHERE empl.DEPTNO = DEPT.DEPTNO + ORDER BY Department, Employee ASC; + +-- Question 7 +SELECT + ENAME as Ename, + JOB as Job, + DEPT.LOC as Loc + FROM EMP + INNER JOIN DEPT + ON DEPT.DEPTNO = EMP.DEPTNO + WHERE DEPT.DNAME = "RESEARCH"; + +-- OUTPUT + +-- Question 1: +-- ENAME DEPTNO +------- ------ +-- CODD 10 +-- DATE 10 +-- ELMASRI 10 + +-- Question 2: +-- ENAME JOB SAL +------- ---------- ------ +-- CODD ANALYST 3000.0 +-- ELMASRI ANALYST 1200.0 +-- SMITH PROGRAMMER 2800.0 +-- DATE PROGRAMMER 1800.0 +-- NAVATHE SALESMAN 2000.0 + +-- Question 3: +-- ΤΜΗΜΑ ΜΕΣΟΣ ΜΙΣΘΟΣ +----- ------------ +-- 10 2000.0 +-- 20 2000.0 +-- 30 2800.0 + +-- Question 4: +-- ΤΜΗΜΑ ПРОΥΠΗΡΕΣΙΑ (ετη) +----- ----------------- +-- 10 25.0 +-- 20 44.0 +-- 30 21.0 + +-- Question 5: +-- PNAME ENAME JOB +----- ------- -------- +-- SALES NAVATHE SALESMAN + +-- Question 6: +-- Department Manager Employee +-- ---------- ------- -------- +-- ACCOUNTING ELMASRI CODD +-- ACCOUNTING ELMASRI DATE +-- ACCOUNTING ELMASRI ELMASRI +-- RESEARCH DATE SMITH +-- SALES NAVATHE NAVATHE + +-- Question 7: +-- Ename Job Loc +----- ---------- ------ +-- SMITH PROGRAMMER ATHENS