{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Exercice" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Importez les données les données se trouvant dans le répertoire `data/hr/`. Ensuite vous pouvez répondre aux questions ci-dessous." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrivez une procédure `proc sql` qui permet d'obtenir un tableau contenant les informations suivantes;\n", "* first_name\n", "* last_name\n", "* department_name,\n", "* city, \n", "* state_province" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMEDEPARTMENT_NAMECITYSTATE_PROVINCE
KimberelyGrant   
BruceErnstITSouthlakeTexas
AlexanderHunoldITSouthlakeTexas
DavidAustinITSouthlakeTexas
DianaLorentzITSouthlakeTexas
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ecrire une procédure `proc sql` pour afficher le prénom, le nom, le numéro de département et le nom du département, pour tous les employés des départements 80 ou 40" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMEDEPARTMENT_IDDEPARTMENT_NAME
EllenAbel80Sales
SundarAnde80Sales
MozheAtkinson50 
DavidAustin60 
HermannBaer70 
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrire une procédure `proc sql` pour afficher les prénoms des employés qui contiennent une lettre z et leur nom de famille, département, ville et état." ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMEDEPARTMENT_NAMECITYSTATE_PROVINCE
MozheAtkinsonShippingSouth San FranciscoCalifornia
HazelPhiltankeShippingSouth San FranciscoCalifornia
ElizabethBatesSalesOX9 9ZBOxford
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrire une procédure `proc sql` pour afficher le prénom et le nom et le salaire pour les employés qui gagnent moins que l'employé gagnent 182." ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMESALARY
JamesLandry2400
StevenMarkle2200
TJOlson2100
KiGee2400
HazelPhiltanke2200
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Trouvez les employés qui gagnent moins que le salaire moyen de tous les employés" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
104BruceErnstBERNST590.423.45681987-06-21IT_PROG6000010360
105DavidAustinDAUSTIN590.423.45691987-06-22IT_PROG4800010360
106ValliPataballaVPATABAL590.423.45601987-06-23IT_PROG4800010360
107DianaLorentzDLORENTZ590.423.55671987-06-24IT_PROG4200010360
115AlexanderKhooAKHOO515.127.45621987-07-02PU_CLERK3100011430
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrire une procédure `proc sql` pour afficher le titre du poste, le nom complet (prénom et nom) de l'employé, et la différence entre le salaire maximum pour l'emploi et le salaire de l'employé. Assurez-vous que les salaire sont sous format `$1,234,456.00`" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
JOB_TITLEEmployee_namesalary_difference
PresidentSteven King$16,000.00
Administration Vice PresidentNeena Kochhar$13,000.00
Administration Vice PresidentLex De Haan$13,000.00
ProgrammerAlexander Hunold$1,000.00
ProgrammerBruce Ernst$4,000.00
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrire une procédure `proc sql` pour afficher le nom du département, le salaire moyen et le nombre d'employés travaillant dans ce département qui ont reçu une commission." ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
DEPARTMENT_NAMESalaireMoyennombre
Shipping$3,475.5645
Sales$8,955.8834
Finance$8,600.006
Purchasing$4,150.006
IT$5,760.005
Executive$19,333.333
Accounting$10,150.002
Marketing$9,500.002
Human Resources$6,500.001
Public Relations$10,000.001
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrire une procédure `proc sql` pour afficher le titre du poste et le salaire moyen des employés." ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
JOB_TITLE  
Accountant$7,920.00
Accounting Manager$12,000.00
Administration Assistant$4,400.00
Administration Vice President$17,000.00
Finance Manager$12,000.00
Human Resources Representative$6,500.00
Marketing Manager$13,000.00
Marketing Representative$6,000.00
President$24,000.00
Programmer$5,760.00
Public Accountant$8,300.00
Public Relations Representative$10,000.00
Purchasing Clerk$2,780.00
Purchasing Manager$11,000.00
Sales Manager$12,200.00
Sales Representative$8,350.00
Shipping Clerk$3,215.00
Stock Clerk$2,785.00
Stock Manager$7,280.00
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "..." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.12" } }, "nbformat": 4, "nbformat_minor": 4 }