Professionnel
-
Résultat Test SQL ANSI
-
SQL ANSI - TEST
https://lamp-dev.com/elance-ansi-sql-code-test-with-answers/109
1. Consider a table named “salary” having the following columns:
“id” (type: INT)
“salary” (type: INT)
“incentive” (type: INT)
“tax” (type: INT)
Write a standard SQL query which will update the tax column with the sum of 10% of salary and 2% of incentive, for those salaries which are more than 15000.
UPDATE salary SET tax = 0.1*salary+0.02*incentive WHERE salary > 15000
2. Consider a table named “employee” having the following columns:
“empid” (type: INT)
“empname” (type: TEXT)
“salary” (type: INT)
Write a standard SQL query which retrieves the empnames whose values start with the string ‘john’ followed by any characters.
SELECT empname FROM employee WHERE empname LIKE 'john%'
3. Consider a table named “employee” having the following columns:
“empid” (type: INT)
“empname” (type: TEXT)
“salary” (type: INT)
Write a standard SQL query which retrieves the number of rows where the salary is not null. The returned value should be represented using the column name “validsalarycount”.
SELECT count(salary) AS validsalarycount FROM employee WHERE salary IS NOT NULL
4. Consider a table named “store” having the following columns:
“storename” (type: TEXT)
“sales” (type: INT)
“Date” (type: DATE)
Write a standard SQL query which retrieves the storenames, whose sales lie between 100 and 2000 (not inclusive). The storenames should not be repeated.
SELECT DISTINCT storename FROM store WHERE sales > 100 AND sales < 2000
5. Consider a table named “staff” having the following column structure:
“empid” (type: INT)
“empname” (type: TEXT)
“salary” (type: INT)
Write a standard SQL query which retrieves the sum of 75 percent of the salaries from the staff table (only salaries above 5000 are to be considered). The returned value should be represented using the column name ‘total’.
SELECT sum(0.75*salary) AS total FROM staff WHERE salary > 5000
6. Consider the following tables:
department
———-
deptid (type: INT)
deptname (type: TEXT)
hours (type: INT)
active (type: BIT)
employee
——–
empid (type: INT)
empname (type: TEXT)
deptid (type: INT)
designation (type: TEXT)
salary (type: INT)
Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.
SELECT e.empname, d.deptname FROM department d INNER JOIN employee e WHERE e.deptid=d.deptid
WHERE e.deptid IN ( SELECT e.deptid FROM employee d GROUP BY e.deptid HAVING COUNT(e.deptid) >=4 ) ORDER BY e.empname ASC;
7. Consider a table called carrecords with the following structure:
name (type: TEXT)
price (type: INT)
color (type: TEXT)
vehicletype (type: TEXT) eg. SEDAN/SUV
A customer wants to see the details (name, price, color, vehicletype) of the vehicles that suit his preferences. This is what he says:
Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.
“If its a black sedan, I’m ready to pay 10,000, but if its red or white, then no more than 8,000. For any other color I won’t go above 7,000, except if its an SUV, in which case my budget is upto 15,000 for a black one or upto 14,000 for any other color.”
SELECT name, price, color, vehicletype FROM carrecords WHERE
(vehicletype = 'SEDAN' AND color = 'black' AND price <= 10000)
OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
OR (vehicletype = 'SUV' AND color = 'black' AND price <= 15000)
OR (vehicletype = 'SUV' AND color != 'black' AND price <= 14000)
OR (price <= 7000)
ORDER BY price ASC
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color = 'BLACK' AND price <= 10000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color IN ('red','white') AND price <= 8000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color NOT IN('RED','WHITE','BLACK') AND price <= 7000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color = 'BLACK' AND price <= 15000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color != 'BLACK' AND price <= 14000)
ORDER BY price ASC;
8. Consider a database with a table called “accounts”, having two fields:
“entrydate” (type: DATE)
“accountno” (type: INT)
Write a SQL query which returns the accountno of the most recent entrydate. The returned value should be represented using the column name, “accountno”.
SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 0,1;
9. Consider a table called “students”, having the following column fields:
“id” (type: INT)
“name” (type: TEXT)
“marks” (type: INT)
Write a SQL query which will calculate the average of the marks of the students passing. The passing criteria is that the marks should be at least 40. The average marks are to be returned using the column name ‘marksaverage’.
SELECT avg(marks) AS marksaverage FROM students WHERE id IN (SELECT id FROM students WHERE marks >= 40)
10. Consider a table called “department”, having the following columns:
“id” (type: INT)
“deptname” (type: TEXT)
“rank” (type: INT)
Write a SQL query which will return the deptnames of the departments whose rank lies between 2 and 5 (inclusive). The results should be returned in increasing order of rank (rank 3 being higher than rank 6).
SELECT deptname FROM department WHERE rank >= 2 AND rank <= 5 ORDER BY rank ASC -
Note de frais en CDI
Se faire rembourser ses notes de frais dans le cadre d'un CDI, c'est possible, en voici la preuve ici.
https://www.juritravail.com/chiffres-et-indices/frais-professionnels-allocations-forfaitaires-limites-exoneration.html
-
Méthode de prospection pour devenir freelance
_s'inscrire sur les sites généralistes https://www.freelance-info.fr/ https://www.freelance.com/,
_candidater aux missions en cours en evnoyant son cv en postulant comme freelance,
_s'inscrire sur les sites des ESN comme HAYS, AKKA, CELAD…Ou encore, les sites des cabinets spécialisés dans le recrutement de freelance (Mushroom, Urban Linker, Agence-e),
_avoir un profil linkedin à jour, un profil malt à jour. -
Tracer sql query dans base oracle
• Pour tracer les requêtes SQL dans une base Oracle il faut passer la commande :
execute dbms_monitor.database_trace_enable(waits=>TRUE,binds=>TRUE,instance_name=>'QUALIF');
• Pour stopper la trace :
execute dbms_monitor.database_trace_disable(instance_name=>'QUALIF');
• La trace sera stockée sous :
select
value
from
v$diag_info
where
name ='Default Trace File';
Exemple :
/export/BD/ORACLE/oratrc/QUALIF/bdump/diag/rdbms/qualif/QUALIF/trace/QUALIF_ora_58689.trc
Elle pourra être lisible avec tkprof :
Syntaxe : tkprof <nom_de_la_trace> <nom_du_fichier_texte_lisible> sys=no
Exemple:
$tkprof QUALIF_ora_7767.trc QUALIF_ora_7767.txt sys=no
L’option sys = no ne garde pas les requêtes lancées par l’utilisateur sys. -
Migration Oracle vers Postgres
-
Règle Boyce Codd et dénormalisation
Une dénormalisation intelligente est basée sur cinq principes de base :
- rajouter des colonnes calculées persistantes, voire des index calculés;
- mettre en place des index couvrants et/ou filtrés;
- utiliser la compression des données;
- utiliser des vues matérialisées (Oracle ou PostgreSQL) ou indexées (SQL Server);
- partitionner les très grandes tables.
-
Quelques commandes bien pratiques